Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
(day(numberic value) of the end date - (( no of weeks between firt day of the date supplied and end date)*2)-(if first day of the month is sunday then 1 else 0) - (if date is saturday then 1 else 0))
*
((remaing days in the month from the date supplied) - ((no of weeks from date supplied and last day of the month)*2) - (if end date is sunday then 1 else 0) - (ifd end date of the month is saturday then 1 else 0))
End date: 03-Jul-2019 (Wednesday)
Running Estimate value:
(3-(0 Weeks *2) - 0 - 0) * (28 - (4*2) - 0-0) = 60
3means enddate -(0 weeks means before 3rd date there is no this month weeks *2)-(0 means before 3rd date there is no any sundayof first week -0means before 3rd date there is no any saturday of first week) * (28means july month of dates 31-3=28 -(4*2 means 28th date of how many weeks there is july month -0 means before 3rd date there is no any sundayof first week -0means before 3rd date there is no any saturday of first week)=60
Note : multifly need *2 doesn't matter
Solved! Go to Solution.
Hi @Anonymous
I’m confused in your sentences, for the same number, you defined them in different calculation. 1VS2, 3VS4:
(3-(0 Weeks *2) - 0 - 0)
(28 - (4*2) - 0-0)
Furthermore, please confirm how do you define the week? If there’s a Sunday appeared before that day (or Sunday = that day), we can count there’s 1 week before? For example, in June 2, there’s 1 week before June 2nd , or there’s 0 week?
Thanks!
Assuming that the first logic is right, and if the week has Sunday, then we call there’s 1 week counted. please follow below steps to generate the results:
I created a fact table with some dates and a calendar table, manage the 1-1 relationship between them.
Add new column:
month = MONTH(Table2[Date]) sunday = IF(Table2[weekday]=6,1,0)
Then add the measures:
isSundayorSaturdayof1stday = var a = DATE(YEAR(MAX(Table2[Date])),MONTH(MAX(Table2[Date])),1) var b = WEEKDAY(a,2) Return IF(ISBLANK(MAX(Table2[Date])),BLANK(),IF(b=7||b=6,1,0)) isSunorSatinend = var a = ENDOFMONTH('Table'[Date]) var b = WEEKDAY(a,2) Return IF(ISBLANK(MAX(Table2[Date])),BLANK(),IF(b=7||b=6,1,0)) leftweeks = var a = ENDOFMONTH('Table'[Date]) var leftdays = CALCULATE(DATEDIFF(MAX(Table2[Date]),a,DAY),VALUES(Table2[month])) Return ROUNDUP(leftdays/7,0) weeks*2 = var a = CALCULATE(SUM(Table2[sunday]),FILTER(ALL(Table2),[Date]<=MAX(Table2[Date])),VALUES(Table2[month])) return a*2 Final value = var a = 3-[weeks*2]-[isSundayorSaturdayof1stday] var b = [leftweeks]*2 var c = 28-b-[isSunorSatinend] Return IF(MAX(Table2[Date])=BLANK(),BLANK(),a*c)
Best regards,
Dina Ye
Hi @Anonymous
If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!
Best regards,
Dina Ye
Hi @Anonymous
I’m confused in your sentences, for the same number, you defined them in different calculation. 1VS2, 3VS4:
(3-(0 Weeks *2) - 0 - 0)
(28 - (4*2) - 0-0)
Furthermore, please confirm how do you define the week? If there’s a Sunday appeared before that day (or Sunday = that day), we can count there’s 1 week before? For example, in June 2, there’s 1 week before June 2nd , or there’s 0 week?
Thanks!
Assuming that the first logic is right, and if the week has Sunday, then we call there’s 1 week counted. please follow below steps to generate the results:
I created a fact table with some dates and a calendar table, manage the 1-1 relationship between them.
Add new column:
month = MONTH(Table2[Date]) sunday = IF(Table2[weekday]=6,1,0)
Then add the measures:
isSundayorSaturdayof1stday = var a = DATE(YEAR(MAX(Table2[Date])),MONTH(MAX(Table2[Date])),1) var b = WEEKDAY(a,2) Return IF(ISBLANK(MAX(Table2[Date])),BLANK(),IF(b=7||b=6,1,0)) isSunorSatinend = var a = ENDOFMONTH('Table'[Date]) var b = WEEKDAY(a,2) Return IF(ISBLANK(MAX(Table2[Date])),BLANK(),IF(b=7||b=6,1,0)) leftweeks = var a = ENDOFMONTH('Table'[Date]) var leftdays = CALCULATE(DATEDIFF(MAX(Table2[Date]),a,DAY),VALUES(Table2[month])) Return ROUNDUP(leftdays/7,0) weeks*2 = var a = CALCULATE(SUM(Table2[sunday]),FILTER(ALL(Table2),[Date]<=MAX(Table2[Date])),VALUES(Table2[month])) return a*2 Final value = var a = 3-[weeks*2]-[isSundayorSaturdayof1stday] var b = [leftweeks]*2 var c = 28-b-[isSunorSatinend] Return IF(MAX(Table2[Date])=BLANK(),BLANK(),a*c)
Best regards,
Dina Ye
Hi @Anonymous
If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!
Best regards,
Dina Ye
User | Count |
---|---|
76 | |
75 | |
46 | |
31 | |
27 |
User | Count |
---|---|
99 | |
91 | |
51 | |
48 | |
47 |