Measure that shows results based on multiple Date filters

[Anonymous]

Hi there,

Im trying to create a measure that would show the DistinctCount of results based on the filters selected by the user. 

There are 3 slicers:

- Year;

-Month;

-Week;

The Goal is to show the total DistinctCount of a collumn, if the value is within the selected range, for example: 
If the user selects 2022, the output would be the total distincts of 2022, but if the user then selected on another filter the month March, the output would be the total distincts for 2022 - March, and same for Weeks. 

Im also trying to do the same but with results that happen AFTER the selected period. So if the user would select 2022, It would display results for 2023, if the user selected March, It would show results for 2023 and after March.

However, if the user deselected year, the results would be filtered according to the other selected filters, ex: All values for March and All values after March. 

Thanks in advance!

[DataVitalizer]

Hi @Anonymous 

You can drag your date column into a slicer, then edit your slicer to use the select a dates in a range or after a specific date, you can see more here https://docs.microsoft.com/en-us/power-bi/create-reports/slicer-filter-relative-time?tabs=powerbi-desktop

Did it work ? 👌 Mark it as a solution to help spreading knowledge 👉 A kudos would be appreciated

[Anonymous]

I can't add more slicers to the page, and the values are to be displayed in a table alongside other values which can't be filtered the same way this collum will.

I tried this metric without success:

Upcoming Waves =

var SelectedDate = Calculate(

max(Waves[Start Date])

,All('Calendar'))

return

if(

max('Calendar'[Date])>SelectedDate

,DISTINCTCOUNT(Waves[Wave ID])

,Blank())

[Anonymous · Accepted solution]

Here is one solution. Create these two measures:

DistinctWaveIDs = DISTINCTCOUNT('Waves'[Wave ID])

DistinctWaveIDs (Upcoming) = 
    VAR _LastDate = LASTDATE('Calendar'[Date])

    RETURN
        CALCULATE(
            [DistinctWaveIDs]
            , 'Calendar'[Date] > _LastDate
        )

Use this data model:

Use this set of test data:

Start Date	.	WaveID
01/01/2022		1
01/03/2022		2
31/03/2022		3
01/04/2022		4
16/04/2022		5
25/12/2022		6
01/01/2023		7
15/01/2023		8
02/03/2023		9
03/03/2023		10
03/03/2023		11
04/03/2023		11

I have two slicers, one for Year, the other for YearAndMonth:

Here are the results:

When I select Year 2022, I correctly get 6 DistinctWaveIDs and 5 for Upcoming (remember that WaveID 11 appears twice in my test data so should only count as 1 in the DistinctCount).

When I select 2022 and March (202203) I correctly get 2 and 8 upcoming.

Hope this helps.

[Anonymous]

Thank you! That worked very well! 🙂