how to write dax with date

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

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