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 

 

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's avatar
Anonymous
Not applicable
6 years ago

 

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