Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good morning
Currently I have a date table and am needing an additional column in my table (or combination of calculated columns and measures) to classify if a day fits the business definition of the last two days of the month so we can report on invoicing raised on these days
In this instance I need to be able to tag a day to be included based on the following
The date table has defined what is a weekday/weekend and every date has its own individual key and date ID – started with the theory of determining 1 (Sat) or 2 (Sun) days back to reclassify each weekend date as the prior Friday giving it the date of the Friday but am hitting a hurdle when I attempt to then tag the final two business days of the month
Any assistance would be greatly appreciated
Thank you
Solved! Go to Solution.
Hi @JBST
There are multiple ways of doing this using Power Query or DAX.
Below is a suggestion using a DAX calculated column added to your existing Date table. This just relies on there being a 'Date'[Date] column, but you could replace parts of this code with references to existing columns.
From your description I think there are three cases to handle:
Hopefully that's right otherwise you can modify the code to suit.
Last 2 days flag = VAR CurrentDayOfMonth = DAY ( 'Date'[Date] ) VAR LastDateOfMonth = EOMONTH ( 'Date'[Date], 0 ) VAR LastDayOfMonth = DAY ( LastDateOfMonth ) VAR WeekdayOfLastDayOfMonth = WEEKDAY ( LastDateOfMonth, 3 ) // Monday=0, Sunday=6 VAR NumberOfDaysToInclude = SWITCH ( TRUE (), WeekdayOfLastDayOfMonth IN {0, 6}, 4, // 4 days if month ends on Mon or Sun WeekdayOfLastDayOfMonth = 5, 3, // 3 days if month ends on Sat 2 // 2 days otherwise ) VAR Include = IF ( LastDayOfMonth - CurrentDayOfMonth + 1 <= NumberOfDaysToInclude, 1, 0 ) RETURN Include
This returns a 0/1 flag but you could change it to true/false or text or whatever you want.
Regards,
Owen
Hi @JBST,
Have you solved your problem by the formula provided by @OwenAuger?
If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you need additional help please share some data sample and expected output, so that we can help further investigate on it?
Best Regards,
Cherry
Hi @JBST
There are multiple ways of doing this using Power Query or DAX.
Below is a suggestion using a DAX calculated column added to your existing Date table. This just relies on there being a 'Date'[Date] column, but you could replace parts of this code with references to existing columns.
From your description I think there are three cases to handle:
Hopefully that's right otherwise you can modify the code to suit.
Last 2 days flag = VAR CurrentDayOfMonth = DAY ( 'Date'[Date] ) VAR LastDateOfMonth = EOMONTH ( 'Date'[Date], 0 ) VAR LastDayOfMonth = DAY ( LastDateOfMonth ) VAR WeekdayOfLastDayOfMonth = WEEKDAY ( LastDateOfMonth, 3 ) // Monday=0, Sunday=6 VAR NumberOfDaysToInclude = SWITCH ( TRUE (), WeekdayOfLastDayOfMonth IN {0, 6}, 4, // 4 days if month ends on Mon or Sun WeekdayOfLastDayOfMonth = 5, 3, // 3 days if month ends on Sat 2 // 2 days otherwise ) VAR Include = IF ( LastDayOfMonth - CurrentDayOfMonth + 1 <= NumberOfDaysToInclude, 1, 0 ) RETURN Include
This returns a 0/1 flag but you could change it to true/false or text or whatever you want.
Regards,
Owen
Hi Owen
Thank you so much for your guidance as this solution works perfectly in all given scenarios within my date table.
Regards
Jaime
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |