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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to calculate custom months with flexible dates

Hi All,

 

I am facing a power BI issue from last few days. I need to calculate custom period calculation. As in I have a date field and month calculation should be the first Sunday of a month to the last Saturday, this could sometimes go for the next month as the period is Sunday to Saturday.

Date
12-08-2020
01-08-2020
06-08-2020
01-08-2020
31-07-2020
28-07-2020
20-06-2020
21-08-2020

04-09-2020

13-06-2020
05-09-2020
25-08-2020
16-06-2020
27-06-2020
18-06-2020

 

For example, in my calc August should be 2nd August(first Sunday of the month) to 5th September(Last Saturday) and July should be 5th July to 1st August.

 

Please help me with a solution of this above problem. 

 

Thanks in advance.

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi @Anonymous ,

 

First create a calendar-dim table,then create a relationship between calendar and fact table;

Then create a column as below:

weekday = WEEKDAY('Table'[Date],2)

And 2 measures as below:

FirstSunday = 
CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[weekday]=7&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))))
Last Saturday = 
var _lastday=EOMONTH(MAX('Table'[Date]),0)
var _weekday=WEEKDAY(_lastday,2)
Return
IF(_weekday<6,CALCULATE(MAX('Calendar-Dim'[Date]),DATEADD(ENDOFMONTH('Calendar-Dim'[Date]),6-_weekday,DAY),ALL('Calendar-Dim'[Date])),CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),_weekday>=6&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date])))))

And you will see:

Screenshot 2020-10-01 115656.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

Hi @Anonymous ,

 

First create a calendar-dim table,then create a relationship between calendar and fact table;

Then create a column as below:

weekday = WEEKDAY('Table'[Date],2)

And 2 measures as below:

FirstSunday = 
CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[weekday]=7&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))))
Last Saturday = 
var _lastday=EOMONTH(MAX('Table'[Date]),0)
var _weekday=WEEKDAY(_lastday,2)
Return
IF(_weekday<6,CALCULATE(MAX('Calendar-Dim'[Date]),DATEADD(ENDOFMONTH('Calendar-Dim'[Date]),6-_weekday,DAY),ALL('Calendar-Dim'[Date])),CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),_weekday>=6&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date])))))

And you will see:

Screenshot 2020-10-01 115656.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

amitchandak
Super User
Super User

@Anonymous , new columns

 

// Firsy Sunday -- Some +/- 1 day is required

New column =
var _st = eomonth([Date], -1)
return
eomonth([Date], -1) +7 -WEEKDAY(_st,1)

 

//Last Saturday
New column =
var _st = eomonth([Date], 0)
return
eomonth([Date], 0) -WEEKDAY(_st,1)

Anonymous
Not applicable

Hi,

Thanks for your reply but last saturday is not last saturday of the month. Please refer my example. Last saturday of the week where the last date of month is there. For example, For August, last saturday should be considered as 5th September as 31st August was on monday, so we need to take that week whichever date is the last Saturday.

 

Please let me know if you still have any doubts.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.