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 

 

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

      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's avatar
        Anonymous
        Not applicable
        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.