Have calculated IF statement based on date slicer

[Anonymous]

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 report with a slicer on Date. When I change this slicer, the calculated column should change as well. Please find below further details on this query.

If anyone could help me out with this issue, it would be much appreciated!

Details:

I'm looking to get the following table as output:

Date (table 1)

Name (table 1)

Style (table 1)

Type (table 1)

Currency (table 1)

Calculated column

Measure for calculated column:

If

Date = earliest filtered date

Then

Value 1 x FX Rate of latest filtered date

Else If

Date = latest filtered date

Then

(Value 1 + 2) x FX Rate of latest filtered date

Else If

Earliest filtered date > Date < latest filtered date

Then

Value 2 x FX Rate of latest filtered date

Tables in Power Query:

Table 1

Name	Currency	Date	Value 1	Value 2
A	I	31/03/2010	200	20
B	O	31/03/2010	100	30
A	P	30/06/2010	50	10
B	P	30/06/2010	160	100
A	O	30/09/2010	80	50
B	I	30/09/2010	10	15

Currency Table

Date	Currency	FX Rate
31/03/2010	I	1
31/03/2010	O	0.1
31/03/2010	P	0.2
30/06/2010	I	0.8
30/06/2010	O	0.15
30/06/2010	P	0.25
30/09/2010	I	0.9
30/09/2010	O	0.3
30/09/2010	P	0.2

[Anonymous]

Hi @Anonymous 

As I can understand, you want calculated column based on slicer selected date.

Columns can not be dynamic they are get loaded at first load only.

IF you want something dynamic you have to use measures.

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]

Hi @Anonymous 

Thank you for your reply, I'm indeed looking to create a measure. Apologies if I sometimes switch up the terminology.

Thanks,

Stan

[Anonymous]

@Anonymous 

What do you mean by earliest date?

As per your statement, let say i select date1 and then afterward i changed slicer values to date2 so you want to create measure for both date1 and date2.

am i right?

If so then In power BI you can't  store previous slicer selection(you called it earliest date).

You can right dax for specific date only (Fixed date or slicer selected date).

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]

Hi @Anonymous 

By earliest & latest date I mean the following:

For the slicer in the BI report, we would like to have a date slicer which is set up as a 'between' type of slicer. In this slicer you can select two dates. This will then show the data between these 2 dates. Please find a picture of the slicer below. Please don't mind the dates that are in the slicer.

Date slicer

I hope to have you informed enough this way. I appreciate all the help you're giving me!

Stan

[Anonymous]

hi @Anonymous 

What i have done is

first i have taken fixed rate into first table by using below dax.

Exchange rate = LOOKUPVALUE('Currency'[FX Rate],'Currency'[Date],Sales[Date],'Currency'[Currency],Sales[Currency])

Exchange rate is column.

Then i Have created date table which is used for taking inputs from user and added it in betweem date slicer as you want it like this.

Date = CALENDAR(MIN(Sales[Date]),MAX(Sales[Date]))

Now the final result is calculated as per your requirement as below.

 

Measure = IF(MIN(Sales[Date])=MIN('Date'[Date]),

SELECTEDVALUE(Sales[Value 1])*SELECTEDVALUE(Sales[Exchange rate])

,IF(MIN(Sales[Date])=MAX('Date'[Date]),

(SELECTEDVALUE(Sales[Value 1])+SELECTEDVALUE(Sales[Value 2]))*SELECTEDVALUE(Sales[Exchange rate]),

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

SELECTEDVALUE(Sales[Value 2])*SELECTEDVALUE(Sales[Exchange rate]))))

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]

Hi @Anonymous 

Thank you lots for your reply, it has helped me to figure the measure out almost completely!

I now have the following:

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])))

The only issue I'm having here is the Exchange Rate. I would like to have all values be multiplied with the Exchange Rate which corresponds to the MAX date that I have filtered. Please find below an example based on the initial dataset:

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:

Date	Currency	FX Rate
30/09/2010	I	0.9
30/09/2010	O	0.3
30/09/2010	P	0.2

 My calculated column would look as follows:

Name	Currency	Date	Value 1	Value 2	Exchange rate	Measure	Comments
A	I	31/03/2010	200	20	0.9	180	value 1
B	O	31/03/2010	100	30	0.3	30	value 1
A	P	30/06/2010	50	10	0.2	2	value 2
B	P	30/06/2010	160	100	0.2	20	value 2
A	O	30/09/2010	80	50	0.3	39	value 1 & 2
B	I	30/09/2010	10	15	0.9	22.5	value 1 & 2

Could you please help me out with achieving this? That would be much appreciated!

Kind regards,

Stan

[Anonymous]

---

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]

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

[Anonymous · Accepted solution]

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]

Hi @Anonymous 

If it resolve your problem mark it as a solution so that it will help others.

Thanks,

Pravin

[Anonymous]

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]

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

[Anonymous]

 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]

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

[Anonymous]

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

[Anonymous]

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

[Pragati11]

Hi @Anonymous ,

Just a basic question here:

1. DATE column from which of the 2 tables is used as a slicer on the report? Table1 or Currency table?

2. I am assuming there is a relationship between these 2 tables

Thanks,

Pragati

[Anonymous]

Hi @Pragati11

Thank you for your reply. Please find below an answer to your questions:

1. The DATE column of Table 1 is used as a slicer

2. The Currency table is only used to apply a currency rate to all calculations based on the latest filtered date. I don't think it will be necessary to create a relationship between these tables, unless this helps with calculating the measure. Always happy to receive any input on this.

Thanks,

Stan