Forum Discussion

ivanhaldaco's avatar
ivanhaldaco
New Member
6 years ago

Using date as a logical test in an IF function in a measure

Hi!

I'm trying to create a calculated measure  (let's call it measure 3) that before one date (let's say November 2018), returns the value of another measure (measure 1) and after that date, returns the value of another measure (measure 2). Both measures 1 and 2 are rolling averages that have been created by using dates in the date table. I want to use the calendar date table as a reference, so ideally my measure would be:

 

Measure 3= IF(Datetable[Date]<DATE(2018,11,01), Measure1, Measure 2)

 

This however seems to be an incorrect DAX syntaxis as I can't reference a date table in the IF function. 

 

I have already tried a workaround adding an index to the data table to each date and doing the following:

Measure 3= Calculate(Measure 1, Datetable[index]<(index number that references november 2018))+Calculate(Measure 2, Datetable[index]>(index number that references november 2018))

However, this doesn't work as well as there are values after and before the indicated index that are being added.

 

Can anyone help me understanding what I'm doing wrong or if there might be another function to do this?

 

2 Replies