Using the displayed date slicer for measure

[aallevena]

Hello All, 

First time posting. I would greatly appreciate some help. 

How can I have my measure select the displayed snapshotDate in the slicer for it's max instead of using the max in the dataset?

For example, the slicer is this: 

I would like to have the forumla be like this:

 CALCULATE(SUM(Table[Plan_NumberOfUnits]),FILTER(Table,Table[snapshotDate]=[High value on slicer, in this case 1/17/2017])

Thanks so much all,

[v-yuezhe-msft]

Hi @aallevena,

Do you want to calculate the sum of all values in Plan_NumberOfUnits column when the date is before the slicer selection date(e.g. 1/17/2017)? If that is the case, create the following measures in your table.

Sumunits = SUM(Table[Plan_NumberOfUnits])
UnitsBeforeSelection = CALCULATE([Sumunits],FILTER(ALL(Table),Table[snapshotDate]<MINX(Table,Table[snapshotDate])))

Then use the UnitsBeforeSelection measure to create visuals, you can check the following screenshot to get more details.

Thanks,
Lydia Zhang

[aallevena]

Hi Lydia, 

Thanks for the reply. 

I would like to only look units on 1/17/2017. If there doesn't exist an entry with 1/17/2017 in the dataset then I want a value of 0 returned. 

Example: 

Suppose there are 10 units on 1/17/2017. I want a value of 10 returned. 

Suppose there are 0 units on 1/17/2017. I want a value of 0 returned. 

Thanks, 

Tony

[v-yuezhe-msft · Accepted solution]

Hi @aallevena,

Change the formula of UnitsBeforeSelection measure to the following formula, then create a card visual using UnitsBeforeSelection measure, and use slicer to filter the card.

UnitsBeforeSelection = CALCULATE([Sumunits],FILTER(ALL(Table),Table[snapshotDate]=MAXX(Table,Table[snapshotDate])))


Thanks,
Lydia Zhang

[Anonymous]

You made my day with MINX and MAXX thx !

[aallevena]

Thanks @v-yuezhe-msft,

I am still getting the issue where if there is no value for snapshot date 1/17/2017 then technically the max option is an earlier date.

Is there a way to pull the value set in the filter as a variable and use that in my equation?

[junglejimsvet]

I know this is old but I have exactly the same problem - I need to take the max value from the filter even if that is higher than the max date in the data set.

e.g. user selects data range 1st to 30th Jan,  but the there were no sales on the 28th, 29th and 30th.  I still want sales per day to be divided by the user selected range (30 days) and not the last date that fits in the range (27)

Cheers,

JB