Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Vijay_A_Verma
Super User
‎06-18-2022 12:44 AM

Last Mon, Tue, Wed, Thu, Fri, Sat and Sun of the Month in DAX

Use Case - I published Last Mon, Tue, Wed, Thu, Fri, Sat and Sun of the Month in Power Query Language M
 which gave formulas for language M. Same formulas are given here for DAX language. There are many business scenarios where you would be asked to find last Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday of the Month.
For example, monthly labour hours report will be published on last Wednesday of the month .  Hence, if a date is given, following should be the answer

Vijay_A_Verma_1-1655537590761.png

Solution -  Following DAX formulas can be used to calculate these dates

Last Monday of the Month
= CEILING(EOMONTH([Date],0)-8,7)+2

Last Tuesdday of the Month
= CEILING(EOMONTH([Date],0)-9,7)+3

Last Wednesday of the Month
= CEILING(EOMONTH([Date],0)-10,7)+4

Last Thursday of the Month
= CEILING(EOMONTH([Date],0)-11,7)+5

Last Friday of the Month
= CEILING(EOMONTH([Date],0)-12,7)+6

Last Saturday of the Month
= CEILING(EOMONTH([Date],0)-13,7)+7

Last Sunday of the Month
= CEILING(EOMONTH([Date],0)-14,7)+8

You can download sample pbix file from https://1drv.ms/u/s!Akd5y6ruJhvhugJllN-eFyxRlIJI?e=01Wdf2 

Comments