Change value to 0 for rows in future dates

Question

Story: Basically I'm trying to showcase the month to date compared to the parallel month along with the delta.

Usually this will only be used for the current month. I was able to add future dates but the values for month to date is repeated for all the days in the future

Objective: I would like the value to be 0 instead of the last value being repeated for the entire future dates

Essentially I would like the following code for my measure but its not working:

_Join MTD = 
IF(
    TODAY()<Current Row date,
    0,
    TOTALMTD(SUM('Fact Table'[Join Count]),'Date Table'[Date])
)

greggyb greggyb · Accepted Answer · 2019-09-12T22:27:05

Measures have no concept of a "current row". Measures are only affected by filter context. This can be confusing at first, because a table visual or a matrix visual has row labels, but these things are contributing filter context. The only place you will find row context is when adding a calculated column to a data table or within an iterator function.

You can probably do something like what I show below, but if you need any more help you'll need to share a sample of your data model (tables, columns, a few sample rows from each, relationships among tables) as well as the visual you're building.

_Join MTD = 
IF(
    TODAY () < MIN ( 'Date Table'[Date] ), // here we find the smallest day in filter
                                           // context with MIN. If there is only one day
                                           // in context, well then that is the min
    0,
    TOTALMTD ( SUM ( 'Fact Table'[Join Count] ), 'Date Table'[Date] )
)

It's a common idiom in DAX to take the MAX or MIN of a column when you want a "currently selected" value, though there are also more selective functions, such as SELECTEDVALUE if you want to guarantee that exactly one value is in filter context.

Edit: Per comments, the goal is to compare to yesterday, not today. We can simply take the measure above and modify its predicate as below:

( TODAY () - 1 ) < MIN ( 'Date Table'[Date] )

Arithmetic operators do reasonable things with date and time types in DAX. Also sometimes useful is that the DATE function deals gracefully with overflow and underflow, per the examples below:

DATE ( 2018, 1, 366 )  // => 2019-01-01
DATE ( 2018, 0, 1 )    // => 2017-12-01
DATE ( 2019, 2, 31 )   // => 2019-03-03
DATE ( 2019, 3, 0 )    // => 2019-02-28

So, if you must, you can abuse this function for date shifting as well, though it's usually a last resort.

Change value to 0 for rows in future dates

1 Answers