DAX for filtering start month and end month data based on slicer selection

[Baarathi88]

Is there a way to create a measure, displaying count users in first month (minimum) of selection of date range with last month (maximum) of selection of date from the date slicer.

Please find the data below and example scenario

For Example : When I select date range from Jan-01-2019 to Feb-02-2019 in date slicer. I need to create 2 measures

1. FirstMonthUsers = DistinctCount(UserName) for Month of Jan-2019

2. LastMonthUsers = DistinctCount(UserName) for Month of Feb -2019 (even though selection is only till Feb-2, i need entire feb month)

Users:

ID	UserName	StartDate	EndDate	CountryID
1	David	1/1/2019		1
2	Prince	1/1/2019		1
3	Kristen	1/1/2019		1
4	Laura	1/1/2019		2
5	Clara	1/1/2019		2
6	Lucus	1/1/2019		3
7	Johnny	1/1/2019		4
8	Sam	1/1/2019		4
9	Peter	1/1/2019		4
10	Benjamin	1/1/2019		5
11	Lucifer	1/1/2019		5
12	Janice	1/1/2019		5
13	Julia	1/1/2019		5
14	Lisa	1/1/2019		5
15	Dhoni	1/1/2019	2/1/2019	1
16	Rahul	1/1/2019	2/1/2019	2
17	Dravid	1/1/2019	2/1/2019	3
18	Sachin	1/1/2019	2/1/2019	4
19	Ganguly	2/28/2019		5
20	Ponting	2/28/2019		1
21	Clarke	2/28/2019		1
22	Khan	2/28/2019		1
23	Akram	2/28/2019		1
24	Jason	2/28/2019		1
25	Pollock	2/28/2019		2
26	James	2/28/2019		2
27	Anderson	2/28/2019		3

[Icey]

Hi @Baarathi88 ,

I have different ideas. I think you're not just trying to filter start and end month data based on slicer selection, but also about start and end date.

For example, you select 1/4/2019 as the minimum value and 3/23/2019 as the maximum value, the min count users should be 18 and the max count users should be 23.

    

If my understanding is right, here’s my idea:

First you create a calendar table:

Calendar = CALENDAR(DATE(2018,1,1),DATE(2020,12,31))

Then you can write your measures as below.

In 'Users'[StartDate] <= EOMONTH ( MAX ( 'Calendar'[Date] ), 0 ) , it means that the start date is less than or equal to the last day of the month, the last day that is included.

In  'Users'[EndDate] > EOMONTH ( MAX ( 'Calendar'[Date] ), -1 ) + 1 , it means that the end date is greater than the first day of the month, the first day of the month is not included.

CountMax =
CALCULATE (
    DISTINCTCOUNT ( Users[UserName] ),
    FILTER (
        ALL ( 'Users' ),
        'Users'[StartDate] <= EOMONTH ( MAX ( 'Calendar'[Date] ), 0 )
            && (
                'Users'[EndDate]
                    > EOMONTH ( MAX ( 'Calendar'[Date] ), -1 ) + 1
                    || 'Users'[EndDate] = BLANK ()
            )
    )
)

CountMin =
CALCULATE (
    DISTINCTCOUNT ( 'Users'[UserName] ),
    FILTER (
        ALL ( 'Users' ),
        'Users'[StartDate] <= EOMONTH ( MIN ( 'Calendar'[Date] ), 0 )
            && (
                'Users'[EndDate]
                    > EOMONTH ( MIN ( 'Calendar'[Date] ), -1 ) + 1
                    || 'Users'[EndDate] = BLANK ()
            )
    )
)

At last the result is as below:

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.

[amitchandak]

@Baarathi88 , Not very clear. See if time intelligence can help

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Next month value =  CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))


diff = [MTD Sales]-[last MTD Sales]

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Baarathi88]

@Ashish_Mathur  Thank You. That helps 😊

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.