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  Pravin,

 

I still have one more question however, which is with regards to an XIRR calculation I now would like to make based on the measure that you helped build. This proves to be more difficult than I thought however. Would you mind helping me with this?

I now have the following table, of which I would like to use the XIRR calculation in order to get 1 figure which will show me the Internal Rate of Return (IRR):

 

NameCurrencyDateValue 1Value 2Measure
A

I

31/03/201020020-160
AI30/06/201020010080
AP30/09/201015000
AO31/12/20101502020
AI31/03/2011100108
AO30/06/20111505050
AP30/09/20111102012
AI31/12/20111202016
AI31/03/201211060136

 

As the XIRR function looks as follows: XIRR(<table>, <values>, <dates>, [guess]) , it asks me to provide a column with values. I unfortunately only have the measure containing the values. If I fill this measure in in the function, I will get an error.

 

Please let me know if you're able to assist me with this. 

Many thanks,

 

Stan Kamerbeek