If statement if date is less than today

[Dayna]

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

[ribisht17 · Accepted solution]

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

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

[Dayna]

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

[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]

@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

[Dayna]

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

[ribisht17]

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

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

Regards,

Ritesh