Forum Discussion

shalabh's avatar
shalabh
Helper I
2 years ago
Solved

Help with active lic calculation

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

IDCloseStartEndQty
101-Jan-2301-Jan-2301-Feb-2310.00
23 jan 2310 jan 2310 apr 235
301-Jan-2301-Feb-2301-Mar-2320.00
401-Feb-2301-Feb-2301-Mar-2330.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

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies