Help with active lic calculation

[shalabh]

Hi,

I am trying to find the # of active lic every quarter.

My raw data has 

1. Close date - this is the date on which the order was close

2. Lic start date - date when lic starts(can be same as close date or in the future)

3. Lic end date - date when lic ends (can be >=close date)

For active lic at any given month, the condition is

1. close date <= the month in question

2. Start date = the month in question

3. end date > the month in question

For example, consider the below table

ID	Close	Start	End	Qty
1	01-Jan-23	01-Jan-23	01-Feb-23	10.00
2	3 jan 23	10 jan 23	10 apr 23	5
3	01-Jan-23	01-Feb-23	01-Mar-23	20.00
4	01-Feb-23	01-Feb-23	01-Mar-23	30.00

Jan active lic is 15 - row 1 and 2 (3rd row is not counted as start date is not in Jan)

Feb active lic is 55 - row 2,3,4

Mar active would be - 5 -row 2

I am struggling to do this in powerbi with DAX. Any help would be greatly appreciated!!

FYI - I have a calendar fiscal table mapped to close date as primary. Start and end date columns are secondary on this

[Anonymous · Accepted solution]

Hi @shalabh ,

I think it's achievable, but you need to sort out your effective conditions. For what you said above, I obtain results that are not consistent with your expectations and can elaborate on what serves as the primary validity condition, as well as other secondary validity conditions.

Measure = var _Close = MONTH(MAX('Table'[Close]))
var _Start = MONTH(MAX('Table'[Start]))
var _End=MONTH(MAX('Table'[End]))
var _Select=SELECTEDVALUE('Table 2'[Date].[MonthNo])
RETURN IF(_Close<=_Select&&_Start=_Select&&_End>_Select,1,0)

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[amitchandak]

@shalabh , Very similar to Active employee in HR, refer to blog or file attached

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU