If statement based on date

[a119526]

Hi - I am putting together a volume variance calculation that normalizes for working days. I've built an IsWorking Day column into my date table to return a value of 1 for working days that I can count for whatever time period I'm analyzing.

IsWorkingDay = IF('Date'[Weekday]>5||'Date'[Holiday]="TRUE",0,1). This works fine.

The issue I'm having is for the current month. 

For the current month, I want to count IsWorkingDay up until the data refresh date. For example, if the data was refreshed on 7/20/2021, I want to count the working days between 7/1/2021 and 7/20/2021 for July. For all other previous months, I would count the full month of IsWorkingDay.

I have a column in my date table with the distinct value of when the data was refreshed.

Any ideas? DAX doesn't seem to want to cooperate with dates in IF statements. 

[Anonymous · Accepted solution]

Hi @a119526 ,

Not very clear about what you mean 'DAX doesn't seem to want to cooperate with dates in IF statements'. 

Can't you use formula like below?

IF(month('table'[date])=month(today()),expression1,expression2)

If I misunderstood your meaning, please share some sample data and the formulas you are using.

Best Regards,

Jay

[amitchandak]

@a119526 , datesmtd can help 

calculate(sum('Date'[IsWorkingDay]), datesmtd('Date'[Date]))

or

MTD= 
var _max = maxx(allselected(Table), Table[Date])//table having data till 20th

var _min =   eomonth(_max,-1) +1
return
CALCULATE(sum('Date'[IsWorkingDay]), FILTER(ALL('Date'),'order'[Date] >= _min && 'Date'[Date] <=_max ) )