How to write Dax (Running Estimate)

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

Hi @Anonymous 

Create a calendar table and connect it with your table

calendar = CALENDARAUTO()

Create columns

day = DAY([end date])

end of this month = ENDOFMONTH('calendar'[Date])

no of weeks1 = INT([day]/7)

no of weeks2 = INT((DAY([end of this month])-[day])/7)

if sunday = var weekday=WEEKDAY([end date],2) return IF(weekday=7,1,0)

if saterday = var weekday=WEEKDAY([end date],2) return IF(weekday=6,1,0)

Column =
 ( [day] - ( 2 * [no of weeks1] ) - [if sunday] - [if saterday] )
    * (
        DATEDIFF ( [end date], [end of this month], DAY ) - ( 2 * [no of weeks2] ) - [if sunday] - [if saterday]
    )

Best Regards
Maggie

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

[parry2k]

@Anonymous Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490