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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ivanhaldaco
New Member

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 2
ahmedoye
Resolver III
Resolver III

@ivanhaldaco , try using something like

 

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

If this answers your question, kindly kudo & mark as solution so others can benefit from it.

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors