Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Our fiscal month end is always the last saturday of the month. I need a calculated column to derive the End of Month (EOM) date based on the transaction date column. The EOM is always the last Saturday of the month. What I can't figure out is how to apply this logic to the prior calendar dates that are after the last calendar month's Satruday. So for example:
Date EOM
7/22/2019 7/27/2019
6/29/2019 6/29/2019
5/26/2019 6/29/2019
5/24/2019 5/25/2019
5/24/2019 5/25/2019
4/30/2019 5/25/2019
4/25/2019 4/27/2019
Here is the excel formula I used.
"=IF(A2>DATE(2011,12,31),IF(A2>IF(WEEKDAY(EOMONTH(A2,0),1)=7,EOMONTH(A2,0),EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1)),IF(WEEKDAY(EOMONTH(EOMONTH(A2,0),1),1)=7,EOMONTH(EOMONTH(A2,0),1),EOMONTH(EOMONTH(A2,0),1)-WEEKDAY(EOMONTH(EOMONTH(A2,0),1),1)),IF(WEEKDAY(EOMONTH(A2,0),1)=7,EOMONTH(A2,0),EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1))),EOMONTH(A2,0))"
Solved! Go to Solution.
So DAX has all of those functions you used in your Excel formula. Therefore you could created a calculated column like the following (you might just need to change the table name in the second line)
EOM2 = VAR A2 = 'Table'[Date] RETURN IF(A2>DATE(2011,12,31),IF(A2>IF(WEEKDAY(EOMONTH(A2,0),1)=7,EOMONTH(A2,0),EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1)),IF(WEEKDAY(EOMONTH(EOMONTH(A2,0),1),1)=7,EOMONTH(EOMONTH(A2,0),1),EOMONTH(EOMONTH(A2,0),1)-WEEKDAY(EOMONTH(EOMONTH(A2,0),1),1)),IF(WEEKDAY(EOMONTH(A2,0),1)=7,EOMONTH(A2,0),EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1))),EOMONTH(A2,0))
So DAX has all of those functions you used in your Excel formula. Therefore you could created a calculated column like the following (you might just need to change the table name in the second line)
EOM2 = VAR A2 = 'Table'[Date] RETURN IF(A2>DATE(2011,12,31),IF(A2>IF(WEEKDAY(EOMONTH(A2,0),1)=7,EOMONTH(A2,0),EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1)),IF(WEEKDAY(EOMONTH(EOMONTH(A2,0),1),1)=7,EOMONTH(EOMONTH(A2,0),1),EOMONTH(EOMONTH(A2,0),1)-WEEKDAY(EOMONTH(EOMONTH(A2,0),1),1)),IF(WEEKDAY(EOMONTH(A2,0),1)=7,EOMONTH(A2,0),EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1))),EOMONTH(A2,0))