How to create a today / this week / this month selector

[osinquinvdm]

I need to provide the user with the ability to easily filter on today / this week / this month / this year / all.

In other words I want to add a “temporality” column.

I was able to do it in power query but I find that the way I did is quite cumbersome.

I created a #"AllDates" table with all the date between the beginning of the project and now.

Then extracted subset for each of the 5 temporalities and combined them in a final #"Calendar" table.

let
    Source = #"AllDates",
    Today = Date.From(DateTime.LocalNow()),
 
    #"Filtered Today" = Table.SelectRows(Source, each Duration.Days(Duration.From([Date]-Today))>=-1),
    #"Added Today" = Table.AddColumn(#"Filtered Today", "Temporalité", each "<24h"),
 
    #"Added All" = Table.AddColumn(Source, "Temporalité", each "All"),
 
    #"Filtered Year" = Table.SelectRows(Source, each Date.IsInCurrentYear([Date])),
    #"Added Year" = Table.AddColumn(#"Filtered Year", "Temporalité", each "This year"),
 
    #"Filtered Month" = Table.SelectRows(Source, each Date.IsInCurrentMonth([Date])),
    #"Added Month" = Table.AddColumn(#"Filtered Month", "Temporalité", each "This month"),
 
    #"Filtered Quarter" = Table.SelectRows(Source, each Date.IsInCurrentQuarter([Date])),
    #"Added Quarter" = Table.AddColumn(#"Filtered Quarter", "Temporalité", each "This Quarter"),
 
    #"Filtered real Week" = Table.SelectRows(Source, each Duration.Days(Duration.From(Today-[Date]))< Date.DayOfWeek(Today)),
    #"Added real Week" = Table.AddColumn(#"Filtered real Week", "Temporalité", each "This week")
 
in
Table.Combine({#"Added Today",#"Added real Week",#"Added Month",#"Added Quarter",#"Added Year",#"Added All"}),

Where it becomes more cumbersome is when I need to add the #"AllDates" table to the model to fix the many-to-many relationship between the #"Calendar" table and the data table.

 

I think I could achieve the same result in DAX, a bit like what the Timeline visual allows, but I don’t know how to approach it.

I found some good examples (including https://community.powerbi.com/t5/Desktop/Time-selector/m-p/97238#M40963) but they all deal with filtering a specific measures, when what I want is to filter the date in my main data, so that ALL measures get filtered.

[v-shex-msft]

Hi @osinquinvdm,

>>but they all deal with filtering a specific measures, when what I want is to filter the date in my main data, so that ALL measures get filtered

By default, slicer operation only supported to find out the equal value. If you want to dio advance filter, you should write a measures to deal with these specify value. It is why the solution only work on specific measures.

For example:

Requirement: get the records which less than chosen value in slicer.
Solution: get selected value, use measure to find the filter range based ont selected value.

Regards,

Xiaoxin Sheng