none
calculate same amount in 2 different period RRS feed

  • Question

  • hello , please , i need your help . 
    I want to display a report with power bi that containt new rows that i will create and in column the values of the total of amount in 2 differents periodes  and it change every time i change the periode . 
    here the result that i want to have 

    but ia have a wrong result 

    and here the querry that i used  

    with ca1 as (select 
    [Code Client],
    sum([Mnt HT]) as cap1,
    cast([Date Doc] as date) as date1
    from [ELEC_AVENIR].[dbo].[View_Ventes] 
    where [Type document] in ('Facture', 'Factue Cpt') and [Date Doc]
    group by [Code Client],cast([Date Doc] as date) ),


    ca2 as (select 
    [Code Client],
    sum([Mnt HT]) as cap2,
    cast([Date Doc] as date) as date2
    from [ELEC_AVENIR].[dbo].[View_Ventes] 
    where [Type document] in ('Facture', 'Factue Cpt') 
    group by [Code Client],cast([Date Doc] as date) )

    select ca1.[Code Client],isnull(ca1.cap1,0)as cap1,ca1.date1 , isnull(ca2.cap2,0)as cap2, ca2.date2 , 
    case 
    when isnull(cap1,0)=0  and cap2 <> 0 then 'nouveaux clients' 

    when cap1 < cap2 then  'en croissance' 

    when cap1 >  cap2 then  'en perte' 

    when cap1 <> 0 and isnull(cap2,0)=0  then 'sans vente' end 'etat' from ca1 full outer join  ca2 on ca1.[Code Client]=ca2.[Code Client]
     and month(ca1.date1)=month(ca2.date2) and day(ca1.date1)=day(ca2.date2) 

    thank you 

    Monday, May 21, 2018 10:45 AM

Answers

All replies