Dynamic status calculation

[mantasceponis]

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)

tables

There are two tables involved in this calculation, one of them has been split into two:

status block:

splited to:

status block T
status block F

table structure:

Service ID (key) | block in (date) | Block out (date) | limited (boolean) | block_id
4083379    | 2015-07-01    | 2015-10-01       |  FALSE             | 7612
4083379          |  2015-12-16     | 2013-01-01       |   TRUE    |  16615 

This table has been split into two by limited value (one for true , other for false)

Services:

service_id | start date |end date
4083379  | 2016-12-10| blank 

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"

table data

Currently the measure used goes like this:
_____________________________________________________________________________________
Service Status =
VAR
STT=[block t period]>0
VAR
STF=[block F period]>0
RETURN
IF
    (
        STT=TRUE(),
       "DEB",
        IF
            (
            STF=TRUE(),
            "SUS",
            "ACT"
            )
    )
 
_____________________________________________________________________________________
 
where:
_____________________________________________________________________________________
block t period =
COUNTROWS
(
    FILTER
        (
            Services,
   COUNTROWS
   (
   FILTER
        (
            'status_block t',
             'status_block t'[block_out]>[Selected_date_TermActive]
             &&
             'status_block t'[block_in]<[Selected_date_TermActive]
        )     
   )>0
        ))
_____________________________________________________________________________________
 and
_____________________________________________________________________________________
block F period =
COUNTROWS
(
    FILTER
        (
            Services,
COUNTROWS
                (
                FILTER
                    (
                    'se_block f',
                    'se_block f'[block_out]>[Selected_date_TermActive]
                    &&
                    'se_block f'[block_in]<[Selected_date_TermActive]
                    )
                ) >0
        ))
_____________________________________________________________________________________
 and
_____________________________________________________________________________________

Selected_date_TermActive = MAX('Dates'[Date])
  
_____________________________________________________________________________________
  
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.

[v-shex-msft · Accepted solution]

HI @mantasceponis,

AFAIK, power bi not support to direct filter on date range who generated by two date column.

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.

Reference:

Spread revenue across period based on start and end date, slice and dase this using different dates

Regards,
Xiaoxin Sheng