The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I am calculating the count of only those ID that has startdate <= 10 and enddate > 10 for each month . In other words need to find count of ID that are active till current selected month. A record is active if its enddate is greater than 10th of that month.
For example for jan month I need to count all those ID that has startdate less than or equal to 10thjan and startdate should contain dates from previous month as well that has end date > 10thjan or in future).
below is the sample data :
Startdate enddate ID
2024-01-10 2024-12-01 a123
2023-12-11 2024-01-11 b123
2024-01-02 2024-11-08 c123
2024-02-11 2024-02-28 d123
2024-03-03 2024-03-10 e123
2024-03-03 2024-03-15 f123
According to above data for jan month the count should be 3, for feb month count should be 0,for march its 1.
Below is the measure I used to calculate count
Num active =
VAR CurrentMonth =
MAX ( 'Date'[Date] )
VAR CutOffDate =
DATE ( YEAR ( CurrentMonth ), MONTH ( CurrentMonth ), 10 )
VAR Result =
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
'Table'[Start date] <= CutOffDate,
'Table'[End date] > CutOffDate
|| ISBLANK ( 'Table'[End date] ),
REMOVEFILTERS ( 'Date' )
)
RETURN
Result
After calculating count of IDs based on start date and end date Now I need further help , I need to calculate percentage average for latest 12 months and 5 months for each category and display in columns as below
Category 12month % average 5 month %age average
A
B
C
Example for category A below is the logic
monthlyPercentage = selected month count/totalcount
%age average = sum (monthly percentage of latest 12 months) /12
Solved! Go to Solution.
Thanks for the reply from lbendlin.
Hi @NG1407 ,
Using the data you provided, I created the following measures:
ActiveCountpast5months =
VAR TodayDate =
TODAY ()
VAR Past10thDates =
ADDCOLUMNS (
CALENDAR ( EDATE ( TodayDate, -5 ), TodayDate ),
"Month10th", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 10 )
)
RETURN
SUMX (
'Table',
IF (
COUNTROWS (
FILTER (
Past10thDates,
DAY ( [Month10th] ) = 10
&& [Month10th] >= 'Table'[Startdate]
&& [Month10th] <= 'Table'[Enddate]
)
) > 0,
1
)
)
%5months average =
DIVIDE ( [ActiveCountpast5months], COUNTROWS ( ALL ( 'Table' ) ) )
ActiveCountpast12months =
VAR TodayDate =
TODAY ()
VAR Past10thDates =
ADDCOLUMNS (
CALENDAR ( EDATE ( TodayDate, -12 ), TodayDate ),
"Month10th", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 10 )
)
RETURN
SUMX (
'Table',
IF (
COUNTROWS (
FILTER (
Past10thDates,
DAY ( [Month10th] ) = 10
&& [Month10th] >= 'Table'[Startdate]
&& [Month10th] <= 'Table'[Enddate]
)
) > 0,
1
)
)
%12months average =
DIVIDE ( [ActiveCountpast12months], COUNTROWS ( ALL ( 'Table' ) ) )
Result:
Best Regards,
Zhu
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply from lbendlin.
Hi @NG1407 ,
Using the data you provided, I created the following measures:
ActiveCountpast5months =
VAR TodayDate =
TODAY ()
VAR Past10thDates =
ADDCOLUMNS (
CALENDAR ( EDATE ( TodayDate, -5 ), TodayDate ),
"Month10th", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 10 )
)
RETURN
SUMX (
'Table',
IF (
COUNTROWS (
FILTER (
Past10thDates,
DAY ( [Month10th] ) = 10
&& [Month10th] >= 'Table'[Startdate]
&& [Month10th] <= 'Table'[Enddate]
)
) > 0,
1
)
)
%5months average =
DIVIDE ( [ActiveCountpast5months], COUNTROWS ( ALL ( 'Table' ) ) )
ActiveCountpast12months =
VAR TodayDate =
TODAY ()
VAR Past10thDates =
ADDCOLUMNS (
CALENDAR ( EDATE ( TodayDate, -12 ), TodayDate ),
"Month10th", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 10 )
)
RETURN
SUMX (
'Table',
IF (
COUNTROWS (
FILTER (
Past10thDates,
DAY ( [Month10th] ) = 10
&& [Month10th] >= 'Table'[Startdate]
&& [Month10th] <= 'Table'[Enddate]
)
) > 0,
1
)
)
%12months average =
DIVIDE ( [ActiveCountpast12months], COUNTROWS ( ALL ( 'Table' ) ) )
Result:
Best Regards,
Zhu
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
User | Count |
---|---|
16 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
8 | |
8 |