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
