Historical status over time

To display the historical status of jobs in Power BI with a timeline chart showing how many jobs were active at the end of each month, you can follow these steps:

Step 1: Prepare Your Data in Power Query

1. Create a Calendar Table:
- In Power Query, generate a calendar table that includes each month’s end date, covering the range of all job start and end dates.
- To do this, create a custom list of dates and set each to the last day of the month.

M Query:

let
StartDate = Date.StartOfMonth(List.Min(Jobs[SSDate])),
EndDate = Date.EndOfMonth(List.Max(Jobs[HODate])),
Months = List.Dates(StartDate, Duration.Days(EndDate - StartDate) / 30 + 1, #duration(30, 0, 0, 0)),
Calendar = Table.FromList(Months, Splitter.SplitByNothing(), {"MonthEnd"})
in
Calendar

2. Expand Calendar with Month-Year:
- Add a column to show the year and month in a readable format (e.g., "YYYY-MM") for easier grouping.

3. Create a Custom Column to Check Job Status for Each Month-End:
- Merge your Jobs table with the Calendar table.
- In the merged table, create a custom column to check if the job was active at the end of each month. Use logic to see if the job’s start date is before or on the month-end date, and the end date (if it exists) is after the month-end date.

M Query:

ActiveJobs = if [SSDate] <= [MonthEnd] and (isnull([HODate]) or [HODate] >= [MonthEnd]) then 1 else 0

4. Group and Aggregate:
- Group the data by `MonthEnd` and sum up the `ActiveJobs` column to get the count of active jobs for each month-end.

Step 2: Create a Visual in Power BI

1. Load the Data:
- Load the transformed data with `MonthEnd` and `ActiveJobs` count into Power BI.

2. Create a Line or Column Chart:
- Use `MonthEnd` as the X-axis and the count of active jobs as the Y-axis.
- This will display a timeline chart showing the number of active jobs at the end of each month.

 Additional Notes
- Ensure that your model is optimized by removing unnecessary columns and setting the data types correctly.
- You can further customize the visual by adding filters, labels, or using tooltips to show more job details on hover.

JS0711 Please mark this as a solution if this help you. Appreciate Kudos

This approach will give you an accurate count of active jobs at each month’s end on the timeline chart.

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.