Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@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)
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |