Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mstan
Frequent Visitor

DAX for Full Weeks Only (Sunday - Saturday) in Previous Month

Hi Everyone,


I am looking for a way to capture data only from days in the (1)previous month which are (2)part of a full Sunday - Saturday week.

 

For example, I would only want to see data from Janury 1st - 28th of 2023, since 29-31 are not part of a full calendar week in January.  Additionally, if the 1st had fallen on any date other than Sunday, I would not want to see that or any dates prior to the first Sunday of the month.

 

mstan_1-1674845690934.png

 

My date table should have everything I need through a combination of Year/Month/WeekNum/WeekdayName/WeekdayNumber/WeekStartDate/WeekEndDate, etc... but I'm having trouble putting it all together.

 

I found this solution, but it appears to be rolling days in the previous month into the days in week count:

week_Max =
IF (
    data[Year_Week] = MAX ( data[Year_Week] );
    "week_Max";
    IF ( data[Year_Week] = MAX ( data[Year_Week-1] ); "week_Max-1" )

 

 If anyone has a solution they can share, I would be greatly appreciative!

2 REPLIES 2
FreemanZ
Super User
Super User

hi @mstan 

What do you going to do with the captured days?

What do you going to do with imcomplete days, like 29-31 Jan and 1-4 Feb?

Thanks for the response!

 

I have to recreate a matrix with statuses, and the average of statuses received for each workday in the previous month, which would be the count of status divided by number of weeks (or count of  weekday) in the month:

 

  Sunday  Monday  Tuesday  Wednesday  Thursday  Friday  Saturday 
Status A 299274256216215253197
Status B 5655938
Status C 72845963887692

 

at the request of my partners, the incomplete days should be omitted from the data completely.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors