Dynamic status calculation

Question

Hi guys,I have a litlle challenge with dynamic statuses and came here for help :)The requirement is to calculate status for a service at a given date (slicer)&nbsp;tablesThere are two tables involved in this calculation,&nbsp;one of them has been split into two:&nbsp;status block: splited to:&nbsp;status block Tstatus block F&nbsp;table structure:&nbsp;Service ID (key) | block in (date) | Block out (date) | limited (boolean) | block_id4083379&nbsp;&nbsp;&nbsp; |&nbsp;2015-07-01&nbsp;&nbsp;&nbsp; | 2015-10-01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; FALSE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 76124083379&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 2015-12-16&nbsp;&nbsp;&nbsp;&nbsp; | 2013-01-01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; TRUE&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp; 16615&nbsp;This table has been split into two by limited value (one for true , other for false)&nbsp;Services:&nbsp;service_id | start date |end date4083379&nbsp;&nbsp;| 2016-12-10| blank&nbsp;Dates:Date table without any relationships to fact.Full case for status calculation:If there is a row in Status block table with TRUE in limited column and it falls into date selection status is "1"If there is a roe in Status Block table with FALSE in limited column and if falls into date selection status is "2"Else status is "3"&nbsp;&nbsp;&nbsp;table data&nbsp;&nbsp;&nbsp;&nbsp;Currently the measure used goes like this:_____________________________________________________________________________________Service Status =VARSTT=[block t period]&gt;0VARSTF=[block F period]&gt;0RETURNIF&nbsp;&nbsp;&nbsp; (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STT=TRUE(),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "DEB",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STF=TRUE(),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "SUS",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "ACT"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; )&nbsp;_____________________________________________________________________________________&nbsp;where:_____________________________________________________________________________________block t period =COUNTROWS(&nbsp;&nbsp;&nbsp; FILTER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Services,&nbsp;&nbsp; COUNTROWS&nbsp;&nbsp; (&nbsp;&nbsp; FILTER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'status_block t',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'status_block t'[block_out]&gt;[Selected_date_TermActive]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'status_block t'[block_in]&lt;[Selected_date_TermActive]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&gt;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ))_____________________________________________________________________________________&nbsp;and_____________________________________________________________________________________block F period =COUNTROWS(&nbsp;&nbsp;&nbsp; FILTER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Services,COUNTROWS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'se_block f',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'se_block f'[block_out]&gt;[Selected_date_TermActive]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'se_block f'[block_in]&lt;[Selected_date_TermActive]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) &gt;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ))_____________________________________________________________________________________&nbsp;and_____________________________________________________________________________________Selected_date_TermActive = MAX('Dates'[Date])&nbsp;&nbsp;_____________________________________________________________________________________&nbsp;&nbsp;So the question is - what is the best way to calculate status in this scenario when user can select the input date?Also - filtering is required. For example show all status "1" items for selected date.The logic of the formulas are fully functioning, the only issue is performance and ability to filter statuses.

anonymous · Accepted Answer

HI&nbsp;mantasceponis,
AFAIK, power bi not support to direct filter on date range who generated by two date column.
&nbsp;
I'd like to suggest you create new table to generate detailed data table and link to original table, then you can direct filter on those date range.
&nbsp;
Reference:
Spread revenue across period based on start and end date, slice and dase this using different dates 
&nbsp;
Regards,Xiaoxin Sheng

Forum Discussion

Dynamic status calculation

1 Reply

Related content

Dynamic Status Calculation

Dynamic Rank Calculation

Dynamic Calculation Different Period

Dynamic Variable in Calculated Columns

Calculate # of days in each status