Forum Discussion

Deva002's avatar
Deva002
Frequent Visitor
9 years ago
Solved

Running total / cumulative Sales

  We have a requirement to create some Running totals in Power BI, we tried several measures but the results were not as expected. how can we achieve this in Power BI?   Solution to this problem ...
  • tringuyenminh92's avatar
    tringuyenminh92
    9 years ago

    Hi Deva002,

     

    Sorry for late response cause i just arrived hometown for new year ^_^

    One of purposes when using external Dates table to allow you filter another fields in same fact table and the cummulative will be reflected. So if your fact table has only 2 columns  Date and Value, i think you could use ALL method and Date column of that.

     

    Cummulative. = CALCULATE(SUM(TableB[Value]),filter(ALL(TableB), TableB[Date]<=MAX(Dates[Date]) ))

    In case you have additional fields and want to filter on that column, you need to handle with ALLEXCEPT, something like this:

     

    Cummulative = CALCULATE(SUM(TableB[Value]),filter(ALLEXCEPT(TableB,TableB[AdditionalField/Dim]), TableB[Date]<=MAX(Dates[Date]) ))

    So my recommendation is using Time Pattern as topic (external dates table):smileyvery-happy:

     

    If this works for you please accept it as solution and also like to give KUDOS.

    Best regards
    Tri Nguyen

Deva002's avatar
Deva002
Frequent Visitor
9 years ago

Dear tringuyenminh92,

 

Can we have a measure like this without a date dimension? 

 

-Deva

 

 

tringuyenminh92's avatar
tringuyenminh92
Icon for Memorable Member rankMemorable Member
9 years ago

Hi Deva002,

 

Sorry for late response cause i just arrived hometown for new year ^_^

One of purposes when using external Dates table to allow you filter another fields in same fact table and the cummulative will be reflected. So if your fact table has only 2 columns  Date and Value, i think you could use ALL method and Date column of that.

 

Cummulative. = CALCULATE(SUM(TableB[Value]),filter(ALL(TableB), TableB[Date]<=MAX(Dates[Date]) ))

In case you have additional fields and want to filter on that column, you need to handle with ALLEXCEPT, something like this:

 

Cummulative = CALCULATE(SUM(TableB[Value]),filter(ALLEXCEPT(TableB,TableB[AdditionalField/Dim]), TableB[Date]<=MAX(Dates[Date]) ))

So my recommendation is using Time Pattern as topic (external dates table):smileyvery-happy:

 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen