Forum Discussion
Helper VIf 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:
Dayna
Try this ...yes you aggregate function..TRY THIS
IF(max('Calendar'[YearWeekNr]) < [Today (Week)], [Sales Value], [Forecast Value]) + 0
6 Replies
- ribisht17
Super User
- 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
DaynaHello 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
- 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]) + 0Many 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
Try this ...yes you aggregate function..TRY THIS
IF(max('Calendar'[YearWeekNr]) < [Today (Week)], [Sales Value], [Forecast Value]) + 0