cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Parreirao2
Helper I
Helper I

Measure that shows results based on multiple Date filters

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!

1 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:

EylesIT_0-1651680214955.png

 

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:

EylesIT_1-1651680301933.png

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).

EylesIT_2-1651680450709.png

 

 

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

EylesIT_3-1651680496056.png

 

Hope this helps.

View solution in original post

4 REPLIES 4
othy_bi
Post Prodigy
Post Prodigy

Hi @Parreirao2 

 

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-de...


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

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())

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:

EylesIT_0-1651680214955.png

 

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:

EylesIT_1-1651680301933.png

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).

EylesIT_2-1651680450709.png

 

 

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

EylesIT_3-1651680496056.png

 

Hope this helps.

Thank you! That worked very well! 🙂

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

Power BI Dev Camp Session 25 with aka link 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, August 25 at 11a PDT for a great session with Ted Pattison!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors