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 

 

Your actual requirement was calculation to be dynamic.

For dynamic calculations we need to use measure as calculated columns are loaded at first load only.

 

so coming to your next requirement, could you please share some sample data and expected output?

 

Thanks,

Pravin

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous 

 

Thank you for your reply.

I'm looking to use the XIRR function based on the measure we have created. Please find this table with measure below:

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

 

When entering the XIRR function in Excel and by using the same table, my output is: 0.833474

I'm looking to get the exact same number in Power BI by using the XIRR function in here using the measure. This means the XIRR needs to be incorporated into a measure which will output this single value.

The issue is, that the XIRR function expects a column instead of a measure as input for the values.

 

Please let me know if you need anything else.

All help would be much appreciated!

 

Stan

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous Pravin,

    It would be greatly appreciated if you could help me with the above! Please let me know if you need anything else.

     

    Kind regards

     

    Stan