Use slicer value to multiply

[Anonymous]

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?

[v-jayw-msft · Accepted solution]

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 

[amitchandak]

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

[FrankAT]

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]

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]

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)

[Anonymous]

hi @FrankAT ,

I belive that is what I'm trying to do:

The calculator indicates that it is the measure, right?

[FrankAT]

Hi @Anonymous 

see figure:

Use the measure (in this example) Multiply by Value in your DAX formula as the multiplier.

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]

@FrankAT  Thanks!

My formula now looks like this: 

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

So, the problem now is that it uses 3 years, becuase of the [AlternateResult] = 3, and it does not change if I change the input in the parameter... Any clues?