Forum Discussion
Have calculated IF statement based on date slicer
Hi Anonymous
Try this measure
Measure 2 =var currencyVALUE=MAX(Sales[Currency])returnIF(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.
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 =
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):
| Name | Currency | Date | Value 1 | Value 2 | Measure |
| A | I | 31/03/2010 | 200 | 20 | -160 |
| A | I | 30/06/2010 | 200 | 100 | 80 |
| A | P | 30/09/2010 | 150 | 0 | 0 |
| A | O | 31/12/2010 | 150 | 20 | 20 |
| A | I | 31/03/2011 | 100 | 10 | 8 |
| A | O | 30/06/2011 | 150 | 50 | 50 |
| A | P | 30/09/2011 | 110 | 20 | 12 |
| A | I | 31/12/2011 | 120 | 20 | 16 |
| A | I | 31/03/2012 | 110 | 60 | 136 |
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
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
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:
Name Currency Date Value 1 Value 2 Measure A I
31/03/2010 200 20 -160 A I 30/06/2010 200 100 80 A P 30/09/2010 150 0 0 A O 31/12/2010 150 20 20 A I 31/03/2011 100 10 8 A O 30/06/2011 150 50 50 A P 30/09/2011 110 20 12 A I 31/12/2011 120 20 16 A I 31/03/2012 110 60 136 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
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