Dynamic flag with a date slicer

[Mario1000]

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!

[Anonymous]

Woow, that is excelent solution! :-).

One question thought, is there a way to use it with the line chart visual?

[Mariusz]

Hi @Mario1000 

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.

[Mario1000]

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!

[saraMissBI]

Hi @Mario1000 ,

You can directly create a quick measure: 

[Mario1000]

Hi @saraMissBI ,

What I need is to flag a condition to filter in further KPI calculations. This condition as said before is that I only want to take into consideration the last date for each store.

@Mariusz proposed this measure ( I just added the last condition for other purposes):

Flag_ = 
VAR __selectedDate = MAX('Calendar'[Date])
VAR Calculo=
INT( 
    MAX(Facts[Date]) = 
    CALCULATE(  
        MAX(Facts[Date]);
        Facts[Date] <= __selectedDate 
    )
)

RETURN
IF(ISBLANK(SUM(Facts[Value]));BLANK();Calculo)

Now this allows me to flag the rows in a table visual, but it's not the final purposes as said in the beginning. I create a KPI which is the sum of values for each store on the last date according to slicer, using the previous flag:

KPI = 
VAR Flag = [Flag_]

RETURN 
CALCULATE(
   SUM(Facts[Value]);
   FILTER('Facts';Flag=1))

But the results are not correct when visualized on bar chart or cards:

So  the question is, how do I use the flag to filter on a KPI measure? Or is there any other workaround?

Thanks!

[Mariusz · Accepted solution]

Hi @Mario1000 

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.

[Mariusz]

Hi @Mario1000 

Also, you can use the filter measure as a visual filter like below, then you don't need to add it to every measure.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Mariusz]

Hi @Mario1000 

Please see the attached file with the solution included.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Mario1000]

Thanks @Mariusz that solved my problem indeed!

[saraMissBI]

Hi @Mario1000 ,

I am sorry my reply was incomplete; I lost some snapshots I included..(something must have gone wrong when posting)

I understand what you need. Could you please have a look at the following snapshots and give me your feedback if that helps to serve what you need?

You can see that you automatically get the sum of "Value" for the stores corresponding to latest dates for each store given the selection in the slicer. I think this solution is a shortcut, please let me know what you think.

Best regards,