Add inactive date to a measure.

[chavanr]

Hi there,

I have a requirement to add inactive date to a measure.

Please see below sample data for Table : Test

So here my "Added Date" is used to create relationship between the calendar table & Test

I need to get the count of occupancies by month year upto 15th date of each month for which i am using the below measure.

measure=

Var _max = EOMONTH(MAXX(ALLSELECTED('Calendar'),'Calendar'[Date]),-1)+15

return

CALCULATE(SUM(TCC_Kerbside[Occupancies]),DATESYTD('Calendar'[Date]),'Calendar'[Date]<= _max)

Now i want to remove the occupancies for which there is inactive date.

e.g. 22 occupancies which were added on 01/07/2021 but became inactive on 21/09/2021 .

and 8 occupancies became inactive on 08/10/2021.

so when a user select october month in the slicer it should do (58594 -(22 +8)) because my reporting month is upto 15th of each month. 

Hope this is helpful.

[v-rongtiep-msft]

Hi @chavanr 

Please provide some data and conditions and why the day is inactive day.

Best Regards

Community Support Team _ polly

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

[chavanr · Accepted solution]

Hi @v-rongtiep-msft 

I created a variable for inactive 

Occupancies =

Var _max = EOMONTH(MAXX(ALLSELECTED('Calendar'),'Calendar'[Date]),-1)+15

Var _cnt_active =

CALCULATE(SUM(Test[Occupancies]),DATESYTD('Calendar'[Date]),

'Calendar'[Date] <= _max)

Var _cnt_active_return = IF(ISBLANK(_cnt_active),0,_cnt_active)

Var _cnt_inactive =

CALCULATE(SUM(Test[Occupancies]),DATESYTD('Calendar'[Date]),

'Test'[InactiveDate] <= _max, Test[InactiveDate] <> BLANK () )

Var _cnt_inactive_return = IF(ISBLANK(_cnt_inactive),0,_cnt_inactive)

return _cnt_active_return - _cnt_inactive_return

[Greg_Deckler]

@chavanr Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[vigneshahob]

Have you tried the it as values? and used not in ?

&& NOT ( Date ) IN Inactive column}

[vigneshahob]

I'm a newbie, have you tried adding one more condition to the filter within calculate function?

CALCULATE(SUM(TCC_Kerbside[Occupancies]),DATESYTD('Calendar'[Date]),'Calendar'[Date]<= _max && 'Calendar'[Date] >Inactive_date) ?