Forum Discussion

SpiroswayGR's avatar
SpiroswayGR
Icon for Resolver III rankResolver III
4 years ago
Solved

SQL query or Dax for Break down running total (cumulative)

Dear community members,   I am trying to create a field or measure that will break down the running total field. For example :  01/02/2022 : Revenue total = 100 02/02/2022 : Revenue total = 300 ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    SpiroswayGR , a new column

    todays revenue =

    var _max = maxx(filter(Table, [Date] < earlier([Date]) ), [Date])

    var _rev = maxx(filter(Table, [Date] =_max ), [revenue]) 

    return

    [revenue] -_rev

SpiroswayGR's avatar
SpiroswayGR
Icon for Resolver III rankResolver III
4 years ago

amitchandak 

Great solution!! Thanks!!

 

serpiva64 Your solution is worked too, but generally I prefer variable solutions. Thanks again for your time!

SpiroswayGR's avatar
SpiroswayGR
Icon for Resolver III rankResolver III
4 years ago

amitchandak 

How can we modify the function if we want to do the same exercise for data that have double dates and extra column country?

For example

 

Variable solution not working well here.

 

serpiva64 but need revision on 1st day for 2nd country

semi working with country

 

  • serpiva64's avatar
    serpiva64
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi,

    Before adding index you have to group by

    then you add the index

    - expand

    - add a custom

    - then extract values

    and apply the necessary steps to get your goal