cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Running (Cumulative) total not working when using date table.

Is there a secret to using a date table when using running totals?

 

Note: I'm using Direct Query, a requirement for this report.

 

I have a table with multiple date fields, which I've unpivoted into a [Date Field] column. And when I use that [Date Field] column for a running total it works fine.

 

The source table had ID, Date Field, and PFA (whether it is a plan actual or forecasted date).

 

But I need to group the counts by month (and the hierarchy doesn't seem to work properly with Direct Query). So I've setup a relationship to a Date Table, but whenever I try to use the "date" from the date table (the two on the right below, with the second grouping by month), it breaks the running total.

 

GnortenJones_1-1598555831185.png

 

I'm using two calculations, the first in an "incremental" count just showing the count of dates (filtered by "Actual" in this case) per day (or month when grouped).  The second is the cumulative which uses the incremental count.

 

Inc Actual = CALCULATE(Count('Table'[Date Field]),Filter('Field Lookup Table',[PFA]="Actual"))

 

**bleep** Actual =
CALCULATE(
[Inc Actual],
FILTER(
ALLSELECTED('Table'[Date Field]),
ISONORAFTER('Table'[Date Field], MAX('Table'[Date Field]), DESC)
)
)

 

I've tried playing with the formulas but haven't found the secret to getting this to work properly.

 

Thanks.

1 ACCEPTED SOLUTION

I've been banging my head against a wall for nearly a week trying to get this to work.  Five minutes after I posted this I think I stumbled upon the solution.

 

I did three things:

 

1)  Instead of using my incremental measure "Inc - Actual" in the running total formula I calculated it in the running total formula itself.

2)  Since I wasn't using my incremental measure, which filtered for "Actual", I added that filter to step 3...

3)  Instead of using Count, i used CountA, and added an additional filter to that to filter only the "Actual" items.

 

Test Runningtotal =
CALCULATE(
COUNTA('Table'[Date Field]),
FILTER(
ALLSELECTED('Date'[YearMonthnumber]),
ISONORAFTER('Date'[YearMonthnumber], MAX('Date'[YearMonthnumber]), DESC)
),FILTER('Lookuptable',[Plan Forecast Actual]="Actual")
)

 

 

Thanks.

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

@GnortenJones - Can you post sample data as text and expected output?

 

They bleeped your measure name!! Shame shame!! 🙂


Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I've been banging my head against a wall for nearly a week trying to get this to work.  Five minutes after I posted this I think I stumbled upon the solution.

 

I did three things:

 

1)  Instead of using my incremental measure "Inc - Actual" in the running total formula I calculated it in the running total formula itself.

2)  Since I wasn't using my incremental measure, which filtered for "Actual", I added that filter to step 3...

3)  Instead of using Count, i used CountA, and added an additional filter to that to filter only the "Actual" items.

 

Test Runningtotal =
CALCULATE(
COUNTA('Table'[Date Field]),
FILTER(
ALLSELECTED('Date'[YearMonthnumber]),
ISONORAFTER('Date'[YearMonthnumber], MAX('Date'[YearMonthnumber]), DESC)
),FILTER('Lookuptable',[Plan Forecast Actual]="Actual")
)

 

 

Thanks.

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors