Forum Discussion

Regular Visitor

9 years ago

Solved

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:

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,

Anonymous
9 years ago
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

6 Replies

Anonymous
Not applicable
9 years ago
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
  Regular Visitor
  9 years ago
  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
  - Anonymous
    Not applicable
    9 years ago
    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