Forum Discussion

chavanr's avatar
chavanr
Icon for Resolver I rankResolver I
4 years ago
Solved

Add inactive date to a measure.

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.

 

 

Need Help
  • chavanr's avatar
    chavanr
    4 years ago

    Hi Anonymous 

     

    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

5 Replies

  • vigneshahob's avatar
    vigneshahob
    Frequent Visitor
    4 years ago

    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) ?

  • vigneshahob's avatar
    vigneshahob
    Frequent Visitor
    4 years ago

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

    && NOT ( Date ) IN Inactive column}

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      chavanr
      Icon for Resolver I rankResolver I
      4 years ago

      Hi Anonymous 

       

      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