Forum Discussion
Dynamic flag with a date slicer
Hi community,
Is it possible to create a dynamic column or something with the same functionality? I need this to flag the rows in a table dinamically depending on a date slicer.
So I have a table with 3 columns: Store, Date and value. Here a simplified example:
| Store | Date | Value |
| A | 01/01/2019 | 1 |
| A | 03/03/2019 | 2 |
| A | 05/05/2019 | 3 |
| B | 02/02/2019 | 4 |
| B | 04/04/2019 | 5 |
| B | 08/08/2019 | 6 |
For all my calculations I need to use only the values in the last date for each store. So I first created a column that flags the last date for each row:
| Store | Date | Value | Flag |
| A | 01/01/2019 | 1 | 0 |
| A | 03/03/2019 | 2 | 0 |
| A | 05/05/2019 | 3 | 1 |
| B | 02/02/2019 | 4 | 0 |
| B | 04/04/2019 | 5 | 0 |
| B | 08/08/2019 | 6 | 1 |
The problem here is that this is an static calculation and what I need is this flag to be referenced to a date slicer in the dashboard, so that if the slicer is set to 06/06/2019 for example, the flag would look like this:
| Store | Date | Value | Flag |
| A | 01/01/2019 | 1 | 0 |
| A | 03/03/2019 | 2 | 0 |
| A | 05/05/2019 | 3 | 1 |
| B | 02/02/2019 | 4 | 0 |
| B | 04/04/2019 | 5 | 1 |
| B | 08/08/2019 | 6 | 0 |
I need the flag to then make some calculations like these:
KPI =
CALCULATE(
SUM(Value)
Filter(Table; Flag=1))
The way the flag is made right now, it considers only the last visit for each store for the current date, but when I go back in time with the slicer it filters the data instead of recalculating the flag.
Is there any way to get this?
I already tried building a table with groupby so that I have the last date for each store but again the same problem, the table is static and doesn't update with the slicer
NOTE = The real data set contains thousands of stores and dates, so any "manual" solution is not useful.
Thank you in advance for your time and help!
Hi Anonymous
Try something like this.
filter = VAR __maxSelectedDate = GROUPBY( CALCULATETABLE( FILTER( 'Table', 'Table'[Date] <= MAX( 'Dates'[Date] ) ), ALLEXCEPT( 'Table', 'Table'[Store] ) ), 'Table'[Store], "@maxDate", MAXX( CURRENTGROUP(), 'Table'[Date] ) ) RETURN CALCULATE( COUNTROWS( 'Table' ), KEEPFILTERS( TREATAS( __maxSelectedDate, 'Table'[Store], 'Table'[Date] ) ) )Sum of filter = CALCULATE( SUM( 'Table'[Value] ), FILTER( 'Table' , [filter] ) )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
10 Replies
- Mariusz
Community Champion
Hi Anonymous
You can create a measure like below.
Flag = VAR __selectedDate = MAX( Dates[Date] ) RETURN INT( MAX( 'Table'[Date] ) = CALCULATE( MAX( 'Table'[Date] ), 'Table'[Date] <= __selectedDate ) )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.Thanks Mariusz !
This measure gets the desired result. However I need the flag to use it as a filter in further calculations. Imagine a simple one, the sum of 'Value' (for the last date on each store). If the flag was a column we would do something like this:
CALCULATE(SUM(Table[Value]);FILTER(Table; Table[Flag]=1))
How can I filter in this case as this flag is a measure?
Thanks!
saraMissBIResolver I
Hi Anonymous ,
You can directly create a quick measure: