cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mantasceponis
Frequent Visitor

Dynamic status calculation

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)
 
tablestables
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 datatable 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.
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors