cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aallevena Frequent Visitor
Frequent Visitor

Using the displayed date slicer for measure

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: 


SnapshotDate.PNG

 

 

 

 

 

 

 

 

 

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,

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Using the displayed date slicer for measure

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])))
1.PNG

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Moderator v-yuezhe-msft
Moderator

Re: Using the displayed date slicer for measure

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.
1.PNG

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
aallevena Frequent Visitor
Frequent Visitor

Re: Using the displayed date slicer for measure

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

Moderator v-yuezhe-msft
Moderator

Re: Using the displayed date slicer for measure

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])))
1.PNG

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
aallevena Frequent Visitor
Frequent Visitor

Re: Using the displayed date slicer for measure

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 Frequent Visitor
Frequent Visitor

Re: Using the displayed date slicer for measure

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

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 79 members 1,494 guests
Please welcome our newest community members: