Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Delta Total ko

Hi there,

 

this is an easy

i've got a number (let's say "sales" for each monh)

i want to calculate the delta between each month vs the previous one

easy peasy

But - logically - the total line is ko as my measure for the previous only works within the context :

 

CALCULATE(sum(sales);PREVIOUSMONTH(date))

without context, this gives nothing

 

I tried to operate with a sumx

pretty sure this is the solution but i can't make it

 

i just need to have the good total : sum of my monthly deltas

 

Example attached with the correct sum i want to get.

 

**bleep** i can't find where to attached my sample pbix !

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Please try

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date Filer]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date Filer],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-1,MONTH))))

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi Anonymous ,

     

    You may create measure like DAX below.

     

    Measure1=
Var Current= CALCULATE(SUM(Table1[sales]),FILTER(ALLSELECTED(Table1),MONTH(Table1[Date]) =MONTH(MAX(Table1[Date]))))
Var Previous= CALCULATE(SUM(Table1[sales]),FILTER(ALLSELECTED(Table1),MONTH(Table1[Date]) =MONTH(MAX(Table1[Date]))-1))
Return
Current-Previous

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Anonymous ,
    Please use they hyperlink symbol to attach your picture after uploading it to one drive or drobbox.
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Please try

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date Filer]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date Filer],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-1,MONTH))))

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You may create measure like DAX below.

     

    Measure1=
Var Current= CALCULATE(SUM(Table1[sales]),FILTER(ALLSELECTED(Table1),MONTH(Table1[Date]) =MONTH(MAX(Table1[Date]))))
Var Previous= CALCULATE(SUM(Table1[sales]),FILTER(ALLSELECTED(Table1),MONTH(Table1[Date]) =MONTH(MAX(Table1[Date]))-1))
Return
Current-Previous

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.