Forum Discussion

JS0711's avatar
JS0711
Frequent Visitor
1 year ago

Historical status over time

Hi all I need help in power bi or power query to show the historical status of jobs that have a start and end date. The data looks like this : The end result / visual I want to show is across...
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi,

Thanks for the solution FarhanJeelani offered, and i want to offer some more information for user to refer to.

hello JS0711 , you can refer to the following solution.

Sample data 

1.Create a calendar table and create 1:n relationship between tables(date->startdate)

Calendar = CALENDAR(DATE(2020,1,1),DATE(2025,12,31))

 

 

2.Create the following measure.

MEASURE =
VAR _table =
    CALCULATETABLE (
        ADDCOLUMNS (
            'Table',
            "flag",
                VAR a =
                    CALENDAR ( [StartDate], [Endate] )
                VAR b =
                    VALUES ( 'Calendar'[Date] )
                VAR c =
                    COUNTROWS ( INTERSECT ( a, b ) )
                RETURN
                    IF ( c > 0, 1, 0 )
        ),
        CROSSFILTER ( 'Calendar'[Date], 'Table'[StartDate], NONE )
    )
RETURN
    SUMX ( _table, [flag] )

3.Then create a chart visual and put the following field to the visual.

 

Output

 

Best Regards!

Yolo Zhu

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