Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
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
v-diye-msft
Community Support
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.

 

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)

05.png

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.

View solution in original post

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.

View solution in original post

2 REPLIES 2
v-diye-msft
Community Support
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.

 

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)

05.png

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.

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.