Why is my running total showing values after specific date?

I currently need to do rather simple running totals up to a certain date. Basically that “Cutoff” data is end of last month. But for demo purposes, I’m using DATE(2020;3;31). Based on: http://portal.enterprisedna.co/courses/313977/lectures/6713829 … I get the following:

    Running Total Actual Cost = 
If(SELECTEDVALUE('Date'[Date]) > DATE(2020;3;31) ; BLANK();
    CALCULATE([Actual Cost];
            FILTER(ALLSELECTED('Date');
                'Date'[Date] <= MAX('Date'[Date]))))

But as you can see ... for some reason, returning blanks after the specified date is ignored or at least, you see the chart just keeps adding months until the end of the date table.

Date Table is using typical:

Date = 
ADDCOLUMNS (
    CALENDARAUTO();
    "DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
    "Year"; YEAR ( [Date] );
    "Monthnumber"; FORMAT ( [Date]; "MM" );
    "Month"; FORMAT ( [Date]; "M" );
    "YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
    "YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
    "MonthNameShort"; FORMAT ( [Date]; "mmm" );
    "MonthNameLong"; FORMAT ( [Date]; "mmmm" );
    "DaysInMonth" ; DAY(  EOMONTH( [Date] ; 0 ));
    "DayOfWeekNumber"; WEEKDAY ( [Date] );
    "DayOfWeek"; FORMAT ( [Date]; "dddd" );
    "DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
    "Quarter"; "Q" & FORMAT ( [Date]; "Q" );
    "YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)

And is set as datetable etc.

What about trying this code to create a date table:

Power BI Date Table Code with Offsets - Jarrett Version.txt (4.9 KB)

You can use “add as table” feature. Remember it’s not DAX.

1 Like

Not sure how that solves the issue of blanks after specific period being ignored.

The only thing I can think of here is it must be to do with the context of the calculation.

The reason that formula works in the tutorial is because the context is the Date column so the SELECTEDVALUE( dateColumn) picks that up.

I honestly think this is the best way to showcase cumulative totals. You’ll see you visual doesn’t look that great when you use MonthYear

To get the formula to work you’ll need the logic after the IF function to work out specifically what YearMonth you are in and then make everything post that BLANK.

Another good thing to check with issues like this is to break out your formula to see what each part of the logic is doing. My guess is if you just have the IF statement and go TRUE, FALSE in the parameters you’ll find that everything is FALSE, that’s why it’s not BLANKing currently.

Thanks
Sam

This Date table contains several date offsets and boolean filters that you could use in measures or in the filter pane on either report, page or visual level to stop values from projecting forward.

The latest version of this extended Date table is available in this thread.


.

To determine what YearMonth you’re in is very simple with the MonthOffset because that value (actual month) will be equal to 0, to stop projecting forward all MonthOffset values should be less than 1.

Thank you … the problem is indeed in the selectedvalue. The value is not selected because I’m using a different column. So I changed it to max(date[date]). I believe/hope it then gets the date of the current record which it then can compare with something else.

Final measure is now:
Running Total Actual Cost (up to previous month) = 
If(max('Date'[Date]) > 'Calculations'[CutOff Date] ; BLANK();
    CALCULATE([Actual Cost];
            FILTER(ALLSELECTED('Date');
                'Date'[Date] <= MAX('Date'[Date]))))

CutOff Date = EOMONTH(TODAY();-1)

Hi Melissa,

Thanks for this. I’m currently still not sure where data tables should be generated (power query or dax). I believe the calendar itself could perfectly be done in power query, but calculations compared to for example today() I’m not to thrilled to have there as you are counting on the idea that it would be refreshed everyday.

Your assumptions are right.
As for any table in your Model it’s considered a best practice to push that as far back to the Source as you possibly can. So if your Source doesn’t provide a Date table (many don’t), the next logical place would be to create that in Power Query…

Out of curiosity - if your Model isn’t refreshed daily, why would it be relevant to compare to TODAY() ?

Hi @Wlknsn , we’ve noticed that no response has been received from you since the 11th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!