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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.