how to write dax with date

[Anonymous]

(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 

[v-diye-msft · Accepted 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)

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.

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 · Accepted 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)

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.

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 · Accepted solution]

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