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

Hi Anonymous Pravin,

 

Thank you very much for all your help. I've learned a tremendous amount from your replies!

I have tweaked the code somewhat in order to have everything working properly, as this part wasn't working properly.

IF(MIN('Date'[Date])>MIN(Sales[Date]) &&MIN(Sales[Date]) <MAX('Date'[Date])

I believe it has to do with a date format not being able to recognise the <> characters.

 

Please find my final code below:

Measure =

var currencyVALUE=MAX(Sales[Currency])
return
IF(MIN(Sales[Date])=MIN('Date'[Date]),
-SELECTEDVALUE(Sales[Value1])*CALCULATE(MAX('Currency Table'[FX Rate]),FILTER('Currency Table','Currency Table'[Date]=MAX('Date'[Date]) && 'Currency Table'[Currency]=currencyVALUE))

,IF(MIN(Sales[Date])=MAX('Date'[Date]),
(SELECTEDVALUE(Sales[Value1])+SELECTEDVALUE(Sales[Value2]))*CALCULATE(MAX('Currency Table'[FX Rate]),FILTER('Currency Table','Currency Table'[Date]=MAX('Date'[Date]) && 'Currency Table'[Currency]=currencyVALUE)),

SELECTEDVALUE(Sales[Value2])*CALCULATE(MAX('Currency Table'[FX Rate]),FILTER('Currency Table','Currency Table'[Date]=MAX('Date'[Date]) && 'Currency Table'[Currency]=currencyVALUE))))
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous 

 

The last condition i hae added because in your main post you have mentioned that condition.

 

I hope you got your measure and expected result.

 

Cheers,

Pravin