Forum Discussion
how to write dax with date
(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
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)
- (if first day of the month is sunday then 1 else 0) - (if date is saturday then 1 else 0))
- (0means before 3rd date there is no any sundayof first week -0means before 3rd date there is no any saturday of first week)
(28 - (4*2) - 0-0)
- (if end date is sunday then 1 else 0) - (ifd end date of the month is saturday then 1 else 0))
- 0means before 3rd date there is no any sundayof first week -0means before 3rd date there is no any saturday of first week)
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
2 Replies
- v-diye-msft
Community Support
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)
- (if first day of the month is sunday then 1 else 0) - (if date is saturday then 1 else 0))
- (0means before 3rd date there is no any sundayof first week -0means before 3rd date there is no any saturday of first week)
(28 - (4*2) - 0-0)
- (if end date is sunday then 1 else 0) - (ifd end date of the month is saturday then 1 else 0))
- 0means before 3rd date there is no any sundayof first week -0means before 3rd date there is no any saturday of first week)
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
- v-diye-msft
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