Hello all,
I have a problem where I have to calculate the running sum of the max value per day of a column. Attaching the excel and the PBIX for reference.I have a column “Value” and it has multiple records for the same day with same value. But when calculating rolling sum, I need to pick the max value for the date and add up the totals for the dates selected. The way I tried to solve is calculating the max value for each day and do a sumx on the max values. I was able to get to the point where I found the max value for the dates selected. But to get the totals of these max values per day , I am stuck. Appreciate your inputs.
Basically, calculating the rolling sum of the max values for the date range selected is what I am trying to calculate. The starting date of the date slicer should not change the values but the values should change with the end date of the date slicer.
Appreciate for your inputs. Not sure if I have to do any data transformations to achieve this result before any DAX.
I’ve been working on this one today, and there are some quirks to it that make it more difficult than it seems at first glance. I have what I think is a sound solution approach, but I need to add an index column to the accounts table in PQ. Thus, if you could please attached the underlying Excel/CSV data file, that would be great.
I can see why this one gave you trouble. It’s a unique pattern that I’ve not seen before, and took me a while to crack. The challenge is not finding the max, which is easy via Group By in Power Query, but finding the first occurrence of that max within a given date. Here’s the way I did it:
using All Rows and Group By identified the max value per date
created an index on the nested tables to count the number of records for each date
created a 1/0 dummy variable equal to one if value equals max value
grouped on the date and dummy variable, and then calculated the min index per date where the dummy variable equaled 1 (i.e. value equals max value)
if value equals max value and date index equals min date index, then returned the initial value otherwise null
Then for the cumulative value just use the standard cumulative value pattern on the first max value calculated in steps 1-5 above.
Also set the slicer to a before rather than a between slicer.
Brian @BrianJ , Kudos to you. Thank you very much for all the support. I looked into the solution, I will need more time to fully understand it and apply to my project solution. To add to what is being done, It is cumulative totals, not just wrt to the day, its cumulative totals for each account per day basis.
What i mean by this is, if i take the matrix visual, when I select 5/19, it should should show
For account 1000 the cumulative max should be 5 and
for account 2000 the cumulative max should be 2500 instead of 2500 for the day irrespective of the account.
And if 9/20 is selected,
For account 1000, it should show 6 and
for account 2000, it should show 7500.
How do we create an index for the day as well as account at the same time?
Also, Is there a different way to limit the date so this data is not affected by the start date instead of using dates before? Keeping the dates between option, if one would also want to look for other values like cost2,cost3 along with total value(cumulative until the end date selected) for specific time duration instead of before date values?
Right now, the Max value changes every time the starting date or end date is changed, the sum of cost2 should vary for the date duration selected but shouldnt change the max value . It should only change with end date selected.
I was able to solve by including the Account in the group by and then creating a calculated column instead of a measure for the running totals and then creating intermediate calculated measure that compares to the most recent date that pulls the max value on the most recent date. That took care of the date filter.
Nice job! I was just planning on getting back to this today, but you beat me to it.
Yes, once the allrows/group by structure is set up, it’s a simple matter to add another level of grouping (in this case Account #) to the mix.
With regard to the date filter, one of my favorite techniques is just to set the slicer up on a disconnected table, so that the slicer no longer directly affects anything on the page. Then you can harvest the slicer value(s) and build them into your relevant DAX measures as filter conditions.
Interesting problem. Enjoyed working with you on this!
Thank you, Brian. Would you mind sharing how you do the disconnected table for dates? I have done that before but not with dates, thanks to EnterpriseDNA. If you could share how you would implement for this problem, it would be great! Or, if you have a video on implementing disconnected table with dates, I will go through it if you can share the link.
I have included disconnected tables in a number of my videos, but I don’t think it’s ever been the primary focus - I should do that though. As I mentioned, this is one of my favorite techniques, and so when I searched just my own posts for “disconnected date tables” I found over three dozen results, so plenty of examples for you to review here:
