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: 

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.

Vijay_A_Verma
Super User
‎06-07-2022 03:29 AM

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

Use Case - I published First 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 first Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday of the Month.
For example, First Thursday of the month, the newsletter for last month's events will be published.  Hence, if a date is given, following should be the answer

FirstDayofMonth.png

 

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

 

First Monday of the Month
= CEILING(EOMONTH([Date],-1)-1,7)+2

First Tuesday of the Month
= CEILING(EOMONTH([Date],-1)-2,7)+3

First Wednesday of the Month
= CEILING(EOMONTH([Date],-1)-3,7)+4

First Thursday of the Month
= CEILING(EOMONTH([Date],-1)-4,7)+5

First Friday of the Month
= CEILING(EOMONTH([Date],-1)-5,7)+6

First Saturday of the Month
= CEILING(EOMONTH([Date],-1)-6,7)+7

First Sunday of the Month
= CEILING(EOMONTH([Date],-1)-7,7)+8

 

You can download sample pbix file from https://1drv.ms/u/s!Akd5y6ruJhvhuX_-lFzt8pUhgpTS?e=8cL4pj 

0