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
Dayna
Helper V
Helper V

If statement if date is less than today

Hello,

 

I have an if statement, where I want to show Sales Value when the weekyearnr is less than the current week. Otherwise, use the Forecast Value.

 

In my QlikView past I'd do:

IF('Calendar'[YearWeekNr]) < [Today (Week)], [Sales Value], [Forecast Value]

 

In PowerBI, this seems to work when looking at all values:

IF(selectedvalue('Calendar'[YearWeekNr]) < [Today (Week)], [Sales Value], [Forecast Value]) + 0
 
This works fine when nothing is filtered, but my values change if I were to use the YearWeekNr slicer.
 
I suspect I'm not doing this correctly, and selectedvalue() isn't the function I need, but I'd appreciate your guidance.
 
Many thanks,
Dayna
1 ACCEPTED SOLUTION
ribisht17
Super User
Super User

Try this ...yes you aggregate function..TRY THIS

 

IF(max('Calendar'[YearWeekNr]) < [Today (Week)][Sales Value][Forecast Value]) + 0

View solution in original post

6 REPLIES 6
ribisht17
Super User
Super User

Try this ...yes you aggregate function..TRY THIS

 

IF(max('Calendar'[YearWeekNr]) < [Today (Week)][Sales Value][Forecast Value]) + 0

Dayna
Helper V
Helper V

Hi @ribisht17 

 

I assumed that too originally, but unless my syntax is wrong, it says it cannot find the field 'Calendar'[YearWeekNr]

 

It is written the same way as what you've listed on your message. Using something like SELECTEDVALUE then renders it fine.

 

I do get a little lost with syntax when referencing a measure compared to that of a field in a table, so it may be something obvious I've not done!

 

Sales Forecast Quantity (Month) =
IF(('Calendar'[YearWeekNr]) < [Today (Month)], [Actual Sales (ISS-SO)], [Forecast Qty]) + 0
 
Many thanks,
Dayna

Looking at this thread, I need an aggregation function to use column fields?

https://community.powerbi.com/t5/Desktop/New-Measure-Cannot-find-name-from-table-column/m-p/2296157 

ribisht17
Super User
Super User

@Dayna 

 

  • The SELECTEDVALUE and VALUES functions read the current filter context, not the row context;
  • When you have a row context, just use a column reference (within RELATED in case it is in a lookup table);
  • If the filter context returns zero rows for the referenced column, then SELECTEDVALUE returns the second argument – do not assume that the second argument is returned only when two or more values are selected;
  • If you use a version of DAX that does not have SELECTEDVALUE, you can use the same pattern as that described in this article, replacing SELECTEDVALUE with the corresponding syntax using HASONEVALUE / VALUES.

Regards,

Ritesh

Hello @ribisht17 

 

Thank you for your response, I've tried but I don't think I fully understand how to use RELATED in this example. You're quite rght that I'm not looking to read the current filter context, but rather the row values.

 

How would RELATED work in my example where I'm evaluating the value of each column to a measure?

 

Many thanks,

Dayna

In your case, you don't need RELATED, remove SELECTEDVALUE

 

IF(('Calendar'[YearWeekNr]) < [Today (Week)][Sales Value][Forecast Value]) + 0

 

Regards,

Ritesh

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