Count of users end of last month slicer

[ArashZ]

Hi Everyone,

The table below shows the active instances per day. I want to calculate the number of instances for each organization at the end of last month. For example, since we haven't reached the end of August yet, on 31 July, Organization A and B each have two instances while C has one. Also, there are two conditions: 1- Active users = Yes, and package = normal. 

Date	Instance	Organization	Active User	Package
22/08/2022	ar	A	Yes	normal
21/08/2022	ar	A	Yes	normal
21/08/2022	ab	A	Yes	normal
21/08/2022	jj	B	No	irregular
20/08/2022	io	B	Yes	normal
19/08/2022	ar	A	Yes	normal
19/08/2022	ab	A	No	normal
19/08/2022	jj	B	No	normal
18/08/2022	ar	A	Yes	normal
18/08/2022	av	C	Yes	normal
17/08/2032	av	C	Yes	irregular
16/08/2022	ar	A	Yes	normal
16/08/2022	ab	A	Yes	normal
16/08/2022	jj	B	Yes	irregular
.	.	.	.	.
.	.	.	.	.
31/07/2022	ar	A	Yes	normal
31/07/2022	ab	A	Yes	normal
31/07/2022	jj	B	Yes	normal
31/07/2022	io	B	Yes	irregular
31/07/2022	av	C	No	normal

The calculated column should show this result:

Date	Organization	Count
31/07/2022	A	2
31/07/2022	B	1
31/07/2022	C	0

Eventually, I want to create a slicer with that calculated column. In fact, the slicer should have a range from 0 to 2.

[Anonymous · Accepted solution]

Hi @ArashZ ,

According to your description, you want to display data for the end of the previous month for the largest date in the current date table. Right？

Here are the steps you can follow：

(1)This is my test data:

(2)Create calculated column - “Count” and “slice”

Count = VAR _END_OF_LAST_MONTH=EOMONTH( MAX('Table1'[Date]),-1)
var _DATE_CURRENT_LINE='Table1'[Date]
return
    IF( _END_OF_LAST_MONTH=_DATE_CURRENT_LINE, IF('Table1'[Active User]="YES" && 'Table1'[Package]="normal" , 1,0),BLANK())

slice = IF(ISBLANK('Table1'[Count]),BLANK(), CALCULATE(SUM(Table1[Count]),ALLEXCEPT(Table1,'Table1'[Organization])))

(3)We put the [Date],[Organization],[Count] fields in the table columns. We should select the aggregation mode [Count] column as “sum”.

(4)If you don’t need the “Total” in this table, you can close the “Totals” in the Format pane .

(5)We put the [slice] in the slice and then will meet your need.

If you need pbix, please click here.

case_01.pbix

Best Regards,

Liu Yang

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

[amitchandak]

@ArashZ , You can get last month end date data using today like

Last Month Today =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1)
return CALCULATE(countrow(table) , FILTER('Date','Date'[Date] = _max))

For complete last month

Last Month Today =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1)
return CALCULATE(countrow(table), FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

add other filters as per need

If you want based on the selection you can use Time Intelligence 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[ArashZ]

@amitchandak Hi,

Thanks for the answer. However, this doesn't show me the result I demonstrated in the question. I want to emphasize that the answer should look like this: