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

[Anonymous]

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.

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.

[Greg_Deckler]

@Anonymous - 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.

[Anonymous · 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.