Day of the month filter for MTD measure not working for all months

[Anonymous]

Hello,

I've created DAX measures that work perfectly except for months with fewer than 31 days (or 29-30 for February) using the following DAX:

Total Executions = CALCULATE(IF(ISBLANK(COUNT(ExecutionLog[ReportID])),0,CALCULATE(COUNT(ExecutionLog[ReportID]))))

Total Executions MTD = CALCULATE([Total Executions],DATESMTD('Calendar'[calendar_date]))

Total Executions Previous MTD = var  _day = DAY(SELECTEDVALUE('Calendar'[caldaynumber])+1) RETURN

CALCULATE([Total Executions MTD],PREVIOUSMONTH('Calendar'[calendar_date]),'Calendar'[caldaynumber] <= _day)

For example, the image below shows the MTD value (and Previous MTD value) for all months through the 30th of each month correctly when I select day number 30, which is the unique values from the 'Calendar'[caldaynumber] column.

However, when I select 31 from the slicer it excludes June, September, and November. Any clue how to still include the last MTD value for the months, i.e. June, September, November, February, and April, that don't have corresponding days in the day number filter. 

[Kristjan76 · Accepted solution]

Hi there,

The problem that you are facing is that when you select any day, you are removing all the dates from the calendar that do not match that day. And since June does not have 31 day there is nothing to show. So what I have done (there might be a better solution out there), is to create a table that contains the days:

Days = GENERATESERIES(1;31;1)

And this is not connected to anything, and then I have created the following measures:

Total Day Executions = 
IF(
    HASONEVALUE(Dates[Date]);
    VAR _Day = SELECTEDVALUE(Days[Days])
    VAR _Date = VALUES(Dates[Date])
    VAR _MonthLastDay = DAY( EOMONTH(_Date;0) )
    VAR _AdjDate = DATE(YEAR(_Date);MONTH(_Date); MIN(_Day;_MonthLastDay))
    RETURN
    IF(
        _AdjDate = _Date;
        CALCULATE(COUNT(Excec[ReportID]);Dates[Date] = _AdjDate)
    )   
)

Total Execution = 
SUMX(
    SUMMARIZE(
        Dates;
        Dates[Date];
        "Exec";[Total Day Executions]
    );
    [Exec]
)

Total MTD = 
IF(
    HASONEVALUE(Dates[Date]);
    VAR _Day = SELECTEDVALUE(Days[Days])
    VAR _Date = VALUES(Dates[Date])
    VAR _MonthLastDay = DAY( EOMONTH(_Date;0) )
    VAR _AdjDate = DATE(YEAR(_Date);MONTH(_Date); MIN(_Day;_MonthLastDay))
    RETURN
    IF(
        _AdjDate = _Date;
        CALCULATE(COUNT(Excec[ReportID]);DATESBETWEEN(Dates[Date];DATE(YEAR(_AdjDate);MONTH(_AdjDate);1); _AdjDate))
    )
    
)

Total PMTD = 
IF(
    HASONEVALUE(Dates[Date]);
    VAR _Day = SELECTEDVALUE(Days[Days])
    VAR _Date = VALUES(Dates[Date])
    VAR _MonthLastDay = DAY( EOMONTH(_Date;0) )
    VAR _AdjDate = DATE(YEAR(_Date);MONTH(_Date); MIN(_Day;_MonthLastDay))
    RETURN
    IF(
        _AdjDate = _Date;
        CALCULATE(COUNT(Excec[ReportID]);DATESBETWEEN(Dates[Date];EDATE(DATE(YEAR(_AdjDate);MONTH(_AdjDate);1);-1); EDATE(_AdjDate;-1)))
    )   
)

I hope that this helps 🙂

NB this is not a complete solution, e.g. currently you have to have one of the days selected, otherwise you will get an error, there are no totals (can be created the same way as Total Exec), etc.

Regards,

Kristjan

[sturlaws · Accepted solution]

Hi, @Anonymous,

when you filter for day 31, months without day 31 is filtered out of your table, so you measure will only return blank. This is by design.

You could workaround it like this:

What you need to do is to add 2 separate tables, 'dimDay' and 'dimMonth', without any relationships to other tables.

You can then create these measures to give you MTD and prevMTD:

MTD = 
CALCULATE (
    SUM ( 'Table'[Executions] );
    FILTER (
        ALL ( dimDate );
        dimDate[DayOfMonth] <= SELECTEDVALUE ( dimDay[Day] )
            && dimDate[Month] = SELECTEDVALUE ( dimMonth[Month] )
    )
)

and 

MTD prev = 
CALCULATE (
    SUM ( 'Table'[Executions] );
    FILTER (
        ALL ( dimDate );
        dimDate[DayOfMonth] <= SELECTEDVALUE ( dimDay[Day] )
            && dimDate[Month]
                = SELECTEDVALUE ( dimMonth[Month] ) - 1
    )
)

here is a demo of how to do it: pbix

Or you can look for an answer here: https://www.daxpatterns.com/time-patterns/

cheers,
S

[Kristjan76 · Accepted solution]

Hi there,

The problem that you are facing is that when you select any day, you are removing all the dates from the calendar that do not match that day. And since June does not have 31 day there is nothing to show. So what I have done (there might be a better solution out there), is to create a table that contains the days:

Days = GENERATESERIES(1;31;1)

And this is not connected to anything, and then I have created the following measures:

Total Day Executions = 
IF(
    HASONEVALUE(Dates[Date]);
    VAR _Day = SELECTEDVALUE(Days[Days])
    VAR _Date = VALUES(Dates[Date])
    VAR _MonthLastDay = DAY( EOMONTH(_Date;0) )
    VAR _AdjDate = DATE(YEAR(_Date);MONTH(_Date); MIN(_Day;_MonthLastDay))
    RETURN
    IF(
        _AdjDate = _Date;
        CALCULATE(COUNT(Excec[ReportID]);Dates[Date] = _AdjDate)
    )   
)

Total Execution = 
SUMX(
    SUMMARIZE(
        Dates;
        Dates[Date];
        "Exec";[Total Day Executions]
    );
    [Exec]
)

Total MTD = 
IF(
    HASONEVALUE(Dates[Date]);
    VAR _Day = SELECTEDVALUE(Days[Days])
    VAR _Date = VALUES(Dates[Date])
    VAR _MonthLastDay = DAY( EOMONTH(_Date;0) )
    VAR _AdjDate = DATE(YEAR(_Date);MONTH(_Date); MIN(_Day;_MonthLastDay))
    RETURN
    IF(
        _AdjDate = _Date;
        CALCULATE(COUNT(Excec[ReportID]);DATESBETWEEN(Dates[Date];DATE(YEAR(_AdjDate);MONTH(_AdjDate);1); _AdjDate))
    )
    
)

Total PMTD = 
IF(
    HASONEVALUE(Dates[Date]);
    VAR _Day = SELECTEDVALUE(Days[Days])
    VAR _Date = VALUES(Dates[Date])
    VAR _MonthLastDay = DAY( EOMONTH(_Date;0) )
    VAR _AdjDate = DATE(YEAR(_Date);MONTH(_Date); MIN(_Day;_MonthLastDay))
    RETURN
    IF(
        _AdjDate = _Date;
        CALCULATE(COUNT(Excec[ReportID]);DATESBETWEEN(Dates[Date];EDATE(DATE(YEAR(_AdjDate);MONTH(_AdjDate);1);-1); EDATE(_AdjDate;-1)))
    )   
)

I hope that this helps 🙂

NB this is not a complete solution, e.g. currently you have to have one of the days selected, otherwise you will get an error, there are no totals (can be created the same way as Total Exec), etc.

Regards,

Kristjan

[Anonymous]

Hello @Kristjan76  and @sturlaws ,

Thank you for your help.  I was able to get it to work using a separate Days table and then created a new measure to handle the context when there are blank days so that it defaults to the current day of the month and includes months with MTD values that are still 0, i.e. blank.

MTD Current = var _day = DAY(TODAY()) RETURN

CALCULATE(MAXX(ADDCOLUMNS(SUMMARIZE('Calendar','Calendar'[calendar_date],'Calendar'[calmonthid]

),

"Total",CALCULATE([MTD ALL DAYS])),[Total]),'Calendar'[caldaynumber]=_day)

Thanks again,

Ben