Forum Discussion

AVIE's avatar
AVIE
Frequent Visitor
5 years ago
Solved

Measure total of difference

Dear,

 

I can't share my data source due to confidentiality, but I'm struggling to get the sum of all differences shown correctly in a measure. 

 

Source data:

Delivery periodEconomical periodValue
January 2020January 2020100
January 2020February 202080
January 2020March 202080
January 2020April 202090

 

Calculating the change in value over economical periods gives this result:

Formula: 

Difference = sum('All Certificates'[Value])-CALCULATE(SUM('All Certificates'[Value]),PREVIOUSMONTH('All Certificates'[Period Economical]))
Result (this is where I got):
Delivery periodEconomical periodDifference
January 2020January 2020100 (since this is the first available value)
January 2020February 2020-20 (100 - 80)
January 2020March 20200 (80 - 80)
January 2020April 202010 (90 - 80)
Total 350

 

The difference per row is correct. However, the "total" row is wrong.

So the total is where it's going wrong. When trying to apply hasonefilter or sumx to correct the totals, I don't seem to get the total of "difference" right. The total should show the sum of differences = 10 (-20 + 0 + 10, the initial value does not need to be taken into account)).


So this is the result I'm looking for:

 

Delivery periodEconomical periodDifference
January 2020January 20200
January 2020February 2020-20 (100 - 80)
January 2020March 20200 (80 - 80)
January 2020April 202010 (90 - 80)
Total 10

 

Can anyone help me out to get the measure of difference right to get this result? Thank you!

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    AVIE , Try diff like

    Diff = SUMX(VALUES('Date'[Period]),if(not(ISBLANK([Prior Value])) && not(ISBLANK([Total value])),[Total value] -[Prior Value],BLANK()))

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    AVIE , for that you need a date Economical Date

     

    If you do have one create like

    Economical Date = "01 " & [Economical period] //Change Data type to date

     

    Join to date table and create measures like

     

    MTD = CALCULATE(SUM(Table[Value]),DATESMTD('Date'[Date]))
    last MTD = CALCULATE(SUM(Table[Value]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

     

    Power BI — Month on Month with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
    https://www.youtube.com/watch?v=6LUBbvcxtKA

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

    • AVIE's avatar
      AVIE
      Frequent Visitor
      5 years ago

      Dear amitchandak ,

       

      Thank you for your response. However, this doesn't work and gets me a wrong result. Can you share a pbix where this leads to the desired result?

      When I replicate your suggestion, it gives me the same result as the one listed under "Value" in my first table above, totaling up to a wrong total of 350.

       

      I have a dates table.

       

      What I need is the result shown in the third table of my original post. Do you know how to get there?