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.
Anonymous's avatar
Anonymous
Not applicable
6 years ago

 

Measure = IF(SELECTEDVALUE('Sales'[Date])=MIN('Date'[Date]),-SELECTEDVALUE(Sales[Value1])*SELECTEDVALUE(Sales[Exchange Rate]),
IF(SELECTEDVALUE('Sales'[Date])=MAX('Date'[Date]),(SELECTEDVALUE(Sales[Value1])+SELECTEDVALUE(Sales[Value2]))*SELECTEDVALUE(Sales[Exchange Rate]),
SELECTEDVALUE(Sales[Value2])*SELECTEDVALUE(Sales[Exchange Rate])))
 
My date slicer is set on the following values: Show values between 31/03/2010 - 30/09/2010
That would mean my measure would take the following FX Rates to multiply with ALL values in my table, no matter which date:
 
As per my understanding you are now checking for only currency not for date.
 
Why don't you calculate it using USERelationship FUnction on currency and calculate the values.
 
Thanks,
Pravin
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous 

 

I'm trying my best to get this figured out but somehow I can't manage...

Could you please show me the Expression that I would need in order to have this working as I displayed in my previous message?

Would be much appreciated!

Thanks,

 

Stan