count when accounts are in use

[GunnerJ]

I'm trying to create a measure that will count distinct accounts that are in use compared to the filter. In the example below looking at just one account I'd want the measure to count '1' if the date was between Nov 5th, 2018 - June 6th, 2019, but '0' from June 7th, 2019 - Feb 10th, 2020. There's then other periods being active and inactive with finally any time the filter is past Sept 17th, 2020 it should show as inactive or '0' towards the counted measure. 

This code was my attempt to create the measure. Var STOP_DT shows the max start date for the account compared to the filter. I wanted to make sure the stop date is greater than the filtered date which would mean then are active. VAR START_DT was supposed to function similarly but it won't let me reference my date column for some reason. 

The end goal hopefully being that if the filtered date is between any row's range of dates to add it to the count.

TEST = 

var STOP_DT = CALCULATE(MAX('Charge Code History'[BI_STOP_DT]),ALLEXCEPT('Charge Code History','Charge Code History'[BI_ACCT]), USERELATIONSHIP('Charge Code History'[BI_STOP_DT],CHARGE_DATES[Date]))

VAR START_DT = CALCULATE(MAX('Charge Code History'[BI_START_DT]), USERELATIONSHIP('Charge Code History'[BI_START_DT],CHARGE_DATES[Date]), 

RETURN CALCULATE(DISTINCTCOUNT('Charge Code History'[BI_ACCT]), 'Charge Code History'[BI_START_DT] <= MAX(CHARGE_DATES[Date]) && STOP_DT > MAX(CHARGE_DATES[Date]))

Any advice would be greatly appreciated. I've provide the link to the file below. Please feel free to ask any questions you may have.

Thanks!

File in the link below

https://www.dropbox.com/s/ctj2tl18www4dch/charge%20code%20test.pbix?dl=0

[GunnerJ · Accepted solution]

@v-henryk-mstf @amitchandak 
Thank you for the replies. I was actually able to resolve the issue a different way and wanted to share it for others to possibly use in the future. 

TEST 2 = 

VAR stop_dt = CALCULATE(max(CHARGE_DATES[Date]), USERELATIONSHIP('Charge Code History'[BI_STOP_DT],CHARGE_DATES[Date]))

VAR start_dt = CALCULATE(max(CHARGE_DATES[Date]), USERELATIONSHIP('Charge Code History'[BI_START_DT],CHARGE_DATES[Date]))

RETURN CALCULATE(DISTINCTCOUNT('Charge Code History'[BI_ACCT]), 'Charge Code History'[BI_START_DT] <= start_dt, 'Charge Code History'[BI_STOP_DT] > stop_dt)

 This code checks whether or not a service had a start date on or before the filtered date and a stop date greater than the filtered date. I've done some testing and it appears to resolve my issue.

Thank you again for your replies. 

[v-henryk-mstf]

Hi @GunnerJ ,

Thank you for your feedback, the solution you provided was very helpful.😊

Best Regards,
Henry

[v-henryk-mstf]

Hi @GunnerJ ,

Whether the advice given by @amitchandak  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[amitchandak]

@GunnerJ , Assume you need two date range slicer, you need two date tables, and at least one of them need to be independent

How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg

Now inactive in date mean not record , Assume you have two independent date table selecting the range

//Date1 is independent Date table, Date is joined with Table
R1 =
var _max = maxx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] <=_max))

//Date2 is independent Date table, Date is joined with Table
R2 =
var _max = maxx(allselected(Date2),Date2[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] <=_max))

present in R1 not in R2

if(not(Isblank([R1])) && isblank([R2]) , [R1], blank())

refer for more code, if needed

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...