Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
Solved! Go to 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
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
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
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
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.
Thanks again,
Ben
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |