Forum Discussion

ybatistamayo's avatar
ybatistamayo
Icon for Helper III rankHelper III
6 years ago
Solved

Daily calculate

I have this dataset and would like to get a calculated column that returns the values ​​in red.
the column "Unit" is a cumulative, what I intend is to subtract then both columns to obtain a value per day.

https://drive.google.com/open?id=1MUHKU3c_Jmu-TqAbmiVhbFYk8xv9SsC0

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi , ybatistamayo 

    Try steps as below:

    1.create index in powerBi edit queries and click "close&&Apply"

     

    2.after sort accending  by index , create calculate column as below:

    Result1 = 
var next_Unit =
CALCULATE(
    SUM('Date'[UNIT]),
    FILTER(
         'Date',
         'Date'[COUNTRY] = EARLIER('Date'[COUNTRY])&&
         'Date'[Index] = EARLIER('Date'[Index])+1
    )
)
return 
If(ISBLANK(next_Unit),0,next_Unit)
    Column = 'Date'[UNIT] -'Date'[Result1]

     

    3.it will show as below

     

    Pbix attached

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

    • ybatistamayo's avatar
      ybatistamayo
      Icon for Helper III rankHelper III
      6 years ago

      sorry it is not the correct result for country A.
      however for country A if it is correct

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

        Actually it did not seems like a cumulative. So I took the next day to the previous day. Can you point the mistake? I can check

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi , ybatistamayo 

    Try steps as below:

    1.create index in powerBi edit queries and click "close&&Apply"

     

    2.after sort accending  by index , create calculate column as below:

    Result1 = 
var next_Unit =
CALCULATE(
    SUM('Date'[UNIT]),
    FILTER(
         'Date',
         'Date'[COUNTRY] = EARLIER('Date'[COUNTRY])&&
         'Date'[Index] = EARLIER('Date'[Index])+1
    )
)
return 
If(ISBLANK(next_Unit),0,next_Unit)
    Column = 'Date'[UNIT] -'Date'[Result1]

     

    3.it will show as below

     

    Pbix attached

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.