Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 rep...
if
measure
Slicer
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous 

     

    Try this measure

     

    Measure 2 =
    var currencyVALUE=MAX(Sales[Currency])
    return
    IF(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.
Pragati11's avatar
Pragati11
Icon for Super User rankSuper User
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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