cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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

2 ACCEPTED SOLUTIONS
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)

1. (if first day of the month is sunday then 1 else 0) - (if date is saturday then 1 else 0))
2. (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)

1. (if end date is sunday then 1 else 0) - (ifd end date of the month is saturday then 1 else 0))
2. 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.

```month = MONTH(Table2[Date])
sunday = IF(Table2[weekday]=6,1,0)```

```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

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Community Support

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

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
2 REPLIES 2
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)

1. (if first day of the month is sunday then 1 else 0) - (if date is saturday then 1 else 0))
2. (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)

1. (if end date is sunday then 1 else 0) - (ifd end date of the month is saturday then 1 else 0))
2. 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.

```month = MONTH(Table2[Date])
sunday = IF(Table2[weekday]=6,1,0)```

```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

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Community Support

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

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.