Forum Discussion

Tom2001's avatar
Tom2001
Regular Visitor
3 years ago

Running total measure

Hi Community,

I'm using the following measure and it's great. The solution was posted in the topic below:
Solved: Re: Running Total solution for slicer - Microsoft Power BI Community

<<<RunningTotal = var thisdate = CALCULATE(MAX(Total[Date]), ALLSELECTED(Total[Account]))
return CALCULATE(SUM(Total[Amount]), ALLEXCEPT(Total,Total[Account]), Total[Date] <= thisdate)>>>

It's providing Running totals for the Accounts for the selected dates range. But, it's cutting off all accounts that could have balances/running totals if  they don't have any sales for the selected dates.
So, I'm facing the situation when I'd need to have all those accounts with balances even if they do not have any sales in the selection range "From ... To ...."
That would be 0 for the Amount with some number for Running Total that came from all previouse/before the selected dates range periods.
Could this measure be modified to do it?
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Tom2001 ,

    I'm not clear about your requirement. Would you like to get the balances/running totals even though there is no sales? Could you please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

    And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community.

    How to upload PBI in Community

    Best Regards

    • Tom2001's avatar
      Tom2001
      Regular Visitor
      3 years ago

      HI yingyinr, 
      Thank you for your reply. Here is a simple table that could explain it.

      AccountsDateSalesRunning Totals
      Account11/1/2022100100
      Account21/2/2022200200
      Account11/3/202250150
      Account51/4/202210001000
      Account21/5/202220002200
      Account31/6/202210001000
      Account21/7/202210003200
       
      Report
      From1/4/2022To1/7/2022
      AccountDatesSaleRunning Total
      Account1 0250
      Account2 30003200
       1/5/202220002200
       1/7/202210003200
      Account3 10001000
       1/6/202210001000
      Account5 10001000
       1/4/202210001000

       

      The Account1 is missing from the report as there was no Sales for the selected dates "From ... To ..." but there is Running Totals/Balance that should be shown. 
      If it's even all 0 it would need to be shown if the account exists. 
      In the old SQL of course it could be left outer and that would be it But, here I hope I could avoide accounts and all dates cross join and merging all together with the data table. For the paginated report it seems as it's good to have just a one flat table as a source.

      Thanks in advance