How to get Cumulative Total to stop at today's date on graph

[Anonymous]

I have a measure that is cumulatively counting the amount of cases created over time. Right now the formula looks like this:

And while this works as intended, it populates for future months on my graph which is the black line below:

So far as of 2/17/2020, there have been 487 cases created but as you can see, 487 is populated for every month. How can I get line on the graph to just stop in February at 487? I don't need it populated for any future dates until we've actually reached those dates. I have a date slicer on the page that is set to the entire year of 2020.

[edhans · Accepted solution]

@Anonymous - see this article by SQLBI on hiding future dates in measures. You can do this either in Power Query or in DAX, but essentially what you'll do is create a column that is TRUE/FALSE for a future date.

In Power Query, add a custom column with the following formula:

= if [Date] > DateTime.Date(DateTime.LocalNow()) then true else false

Or, in DAX (not recommended unless you don't have access to Power Query as DAX calculated columns generally don't perform as well in the overall model):

IsFutureDAX = 
if(
    DATE[Date] > TODAY(),
    TRUE(),
    FALSE()
)

 Then you simply filter out the TRUE values in measures. For example in this pseudocode:

Test Measure =
CALCULATE(
    SUM(Sales[Sales Amount),
    DATE[IsFuture] = FALSE()
)

You can also use either IsFuture or IsFutureDAX columns as a filter for visuals by dropping it in the filter and excluding the TRUE values. The dates and IsFuture columns will automatically update with each report refresh.

[Greg_Deckler]

Try modifying your FILTER clause to be:

FILTER(ALLSELECTED(Opportunity), Opportunity[Value] <= MAX(Date[Date]) && Opportunity[Value] <= TODAY())

[Anonymous]

Hi @Greg_Deckler I tried that and it unfortunately returned the same result. @amitchandak I tried your suggestion as well and doing it that way doesn't make it a cumulative total but just totals for each particular month. 

[amitchandak]

I went ahead and added a visual level filter of date. And use a relative date filter. And set it in the last 5 years. That is my data max.

[Greg_Deckler]

Going to need sample data to test with then. 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

[amitchandak]

Replace max(Date[Date]) with Today()

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin