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 

 

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

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

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