cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eishman
Frequent Visitor

Align Day and Date according to 1st Business Day of Each Month

Hi

I am stuck with the following problem.

I am trying to align daily app login stats according to the week of the login.

 

Example : 

Monday 3 Feb | Week 1 of February | 7 Login 

    would need to be compared to 

Monday 2 March | Week 1 of March | 5 Logins

 

And then all subsequent days would match according to the day of the week ie Tuesday 4 Feb vs Tuesday 3 March.

 

I am able to create a measure and/or column to identify the week no, the week no of the month, the 1st working day of the month, 1st Sunday or Monday of the month etc, but cannot incorporate this into a visual that matched accordingly.

 

Any guidance would be appreciated. 

 

3 REPLIES 3
amitchandak
Super User
Super User

Create a working day in calendar

Work Day

 

Then have new column like

Work day No = countx(filter(date,Date[Month year] = earlier(Date[Month year]) &&  Date[Date] <= earlier(Date[Date])),date[Work day])

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Thanks, appreciate the quick response. 

 

In your example :

What does [Work day] column hold ?

Month year is just a column : FORMAT('Date'[Date],"mmm yyyy") ?

 

I use the following measure to get the 1st Monday of the each month

 

Is 1st Monday =
VAR a = MIN('Date'[Date])
VAR y = FILTER(
ALL('Date'[Date]),
YEAR('Date'[Date]) = YEAR(a) &&
MONTH('Date'[Date]) = MONTH(a) &&
DAY([Date]) > 0 &&
DAY([Date]) < 8 &&
WEEKDAY([Date],2) = 1)
RETURN MAXX(y,[Date])

 

I can't upload file unfortunately as our network access does not allow for it

@eishman ,

 

Could you please paste some sample data and clarify more details about the expected result?

 

Regards,

Jimmy Tao

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors