Forum Discussion
Have calculated IF statement based on date slicer
Hi all,
I'm fairly new to DAX. I'm looking to have a calculated IF statement based on filters I apply in the report. I have 2 tables in Power Query and I'm looking to get 1 output table in my report with a slicer on Date. When I change this slicer, the calculated column should change as well. Please find below further details on this query.
If anyone could help me out with this issue, it would be much appreciated!
Details:
I'm looking to get the following table as output:
Date (table 1)
Name (table 1)
Style (table 1)
Type (table 1)
Currency (table 1)
Calculated column
Measure for calculated column:
If
Date = earliest filtered date
Then
Value 1 x FX Rate of latest filtered date
Else If
Date = latest filtered date
Then
(Value 1 + 2) x FX Rate of latest filtered date
Else If
Earliest filtered date > Date < latest filtered date
Then
Value 2 x FX Rate of latest filtered date
Tables in Power Query:
Table 1
Name | Currency | Date | Value 1 | Value 2 |
A | I | 31/03/2010 | 200 | 20 |
B | O | 31/03/2010 | 100 | 30 |
A | P | 30/06/2010 | 50 | 10 |
B | P | 30/06/2010 | 160 | 100 |
A | O | 30/09/2010 | 80 | 50 |
B | I | 30/09/2010 | 10 | 15 |
Currency Table
Date | Currency | FX Rate |
31/03/2010 | I | 1 |
31/03/2010 | O | 0.1 |
31/03/2010 | P | 0.2 |
30/06/2010 | I | 0.8 |
30/06/2010 | O | 0.15 |
30/06/2010 | P | 0.25 |
30/09/2010 | I | 0.9 |
30/09/2010 | O | 0.3 |
30/09/2010 | P | 0.2 |
Hi Anonymous
Try this measure
Measure 2 =var currencyVALUE=MAX(Sales[Currency])returnIF(MIN(Sales[Date])=MIN('Date'[Date]),SELECTEDVALUE(Sales[Value 1])*CALCULATE(MAX('Currency'[FX Rate]),FILTER('Currency','Currency'[Date]=MAX('Date'[Date]) && 'Currency'[Currency]=currencyVALUE)),IF(MIN(Sales[Date])=MAX('Date'[Date]),(SELECTEDVALUE(Sales[Value 1])+SELECTEDVALUE(Sales[Value 2]))*CALCULATE(MAX('Currency'[FX Rate]),FILTER('Currency','Currency'[Date]=MAX('Date'[Date]) && 'Currency'[Currency]=currencyVALUE)),IF(MIN('Date'[Date])>MIN(Sales[Date]) &&MIN(Sales[Date]) <MAX('Date'[Date]),SELECTEDVALUE(Sales[Value 2])*CALCULATE(MAX('Currency'[FX Rate]),FILTER('Currency','Currency'[Date]=MAX('Date'[Date]) && 'Currency'[Currency]=currencyVALUE)))))Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
18 Replies
Hi Anonymous ,
Just a basic question here:
1. DATE column from which of the 2 tables is used as a slicer on the report? Table1 or Currency table?
2. I am assuming there is a relationship between these 2 tables
Thanks,
Pragati
Hi Pragati11
Thank you for your reply. Please find below an answer to your questions:
1. The DATE column of Table 1 is used as a slicer
2. The Currency table is only used to apply a currency rate to all calculations based on the latest filtered date. I don't think it will be necessary to create a relationship between these tables, unless this helps with calculating the measure. Always happy to receive any input on this.
Thanks,
Stan
Hi Anonymous
As I can understand, you want calculated column based on slicer selected date.
Columns can not be dynamic they are get loaded at first load only.
IF you want something dynamic you have to use measures.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.Anonymous
What do you mean by earliest date?
As per your statement, let say i select date1 and then afterward i changed slicer values to date2 so you want to create measure for both date1 and date2.
am i right?
If so then In power BI you can't store previous slicer selection(you called it earliest date).
You can right dax for specific date only (Fixed date or slicer selected date).
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
