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 

 

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'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