get count of words based on user currently period selection

[rafabg]

Hi,

What I need to do is get the top 10 words from a period prior than the currently selected one.

For ex: if the selected period is 5/5/2020 to 5/7/2020, I wanna check the top 10 count of words from 5/2/2020 to 5/4/2020.

I was trying to use CALCULATETABLE to filter the datetime columns using the max and min (selected) date as reference, but as I understand, this is not possible, cuz the CALCULETABLE does not work with context based filters (it returns the whole table).

Here is an example of the data:

word	date
atendimentos	05/07/2020
explica	05/07/2020
explica	05/05/2020
municipios	05/06/2020
numeros	05/05/2020
municipios	05/05/2020
atendimentos	05/05/2020
municipios	05/05/2020
numeros	05/04/2020
atendimentos	05/04/2020
atendimentos	05/02/2020
explica	05/03/2020
numeros	05/02/2020
numeros	05/02/2020
explica	05/02/2020
explica	05/04/2020
atendimentos	05/04/2020

The currenctly is:

05/05 to 05/07/2020
municipios	3
explica	2
atendimentos	2
numeros	1

The result I want is:

05/02 to 05/04/2020
atendimentos	3
explica	3
numeros	3

I was trying to make a table with rows from 1 to 10 (n), and using this formula:

CALCULATE(FIRSTNONBLANK([word],0),SAMPLE(1,TOPN(SELECTEDVALUE(n),CALCULATETABLE(SUMMARIZE(words,word,"qnt",COUNTA([word])),[date]>=priorDateStart,[date]<=priorDateEnd),[qnt],DESC),[qnt],ASC))

Is there a way to make this to work, considering that it must be dynamic?

Thanks!

[Icey · Accepted solution]

Hi @rafabg ,

Sorry, the previous reply ignored some conditions. This should meet your needs:

1. Create a calendar table.

Calendar =
CALENDAR ( MIN ( 'Table'[date] ), MAX ( 'Table'[date] ) )

2. Create Measures.

Count = 
VAR maxdate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
VAR mindate =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
VAR days =
    DATEDIFF ( mindate, maxdate, DAY )
VAR previousmaxdate = maxdate - days - 1
VAR previousmindate = mindate - days - 1
VAR table1 =
    FILTER ( 'Table', [date] <= previousmaxdate && [date] >= previousmindate )
RETURN
    COUNTROWS ( table1 )

Top10Count = 
CALCULATE (
    [Count],
    FILTER (
        VALUES ( 'Table'[word] ),
        IF ( RANKX ( ALL ( 'Table'[word] ), [Count],, DESC ) <= 10, [Count], BLANK () )
    )
)

3. The result is as follows. The slicer is created with Date column in Calendar table.

You can check more details from here.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Icey]

Hi @rafabg ,

Please kindly check if the attachment is what you want.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FrankAT]

Hi @rafabg 

is that whar you are looking for?

Regards FrankAT

[rafabg]

@FrankAT  Hello, no, I would like to show the past period, based on currently selection...the main problem, as I see it, is that the period is not regular, it can be 1, 2, 3, 4 days, and so on...

If the user select a range of 7 days, for example, I wanna return the top 10 count of words from the previous period (7 days before the selected range).