Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Use slicer value to multiply

Hi,

 

I use a CALENDAR function to create a calendar from the last date in my table and 365 days into the future, based on this formula:

 
FutureAUM = CALENDAR(LASTDATE(Holdings[AsOfDate]),LASTDATE(Holdings[AsOfDate])+365)
 
What I want is for the users to be able to use a slicer drop down to select how many years the 365 days should be multiplied with. For example, if the user selects 2 years from the drop down menu, the formula should look like this:
 
FutureAUM = CALENDAR(LASTDATE(Holdings[AsOfDate]),LASTDATE(Holdings[AsOfDate])+(365 * 2))
 
It all works with the 365, but I'm not able to make it work with (365 * User input).

 

Any recommendations?

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    You could use SELECTEDVALUE() function.

    Create a table with columns contains values (1,2,3,4,5,...) adn use this column as dropdown slicer.

    Modify your formula as below.

    FutureAUM = CALENDAR(LASTDATE(Holdings[AsOfDate]),LASTDATE(Holdings[AsOfDate])+365*SELECTEDVALUE('slicer'[slicer]))

     

    Best Regards,

    Jay 

8 Replies

  • FrankAT's avatar
    FrankAT
    Community Champion
    5 years ago

    Hi Anonymous 

    create a new parmeter on tab Modeling. Power BI Desktop creates a new table with measure to use as multiplier. Insert this measure into your DAX formula.

     

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi FrankAT,

       

      Thanks for the reply! 

       

      Like this? 

      FutureAUM = CALENDAR(LASTDATE(Holdings[AsOfDate]),LASTDATE(Holdings[AsOfDate])+(365*nYears[nYears Value]))
       
       
       
      I chose the slicer to be 2 in this case. If I do it like that, the graph dissapears. However, if I use 
      FutureAUM = CALENDAR(LASTDATE(Holdings[AsOfDate]),LASTDATE(Holdings[AsOfDate])+(365*2)), the graph expands to two years.

       

       

       

      • FrankAT's avatar
        FrankAT
        Community Champion
        5 years ago

        Hi Anonymous 

        use the automatic created measure not the table column!

        With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
        FrankAT (Proud to be a Datanaut)

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , if you are creating a table. I doubt you can use slicer parameters

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    You could use SELECTEDVALUE() function.

    Create a table with columns contains values (1,2,3,4,5,...) adn use this column as dropdown slicer.

    Modify your formula as below.

    FutureAUM = CALENDAR(LASTDATE(Holdings[AsOfDate]),LASTDATE(Holdings[AsOfDate])+365*SELECTEDVALUE('slicer'[slicer]))

     

    Best Regards,

    Jay