Historical status over time

[JS0711]

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 a timeline chart, how many jobs were Active at the end of each month

Thanks in advance

[Anonymous]

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.

[FarhanJeelani]

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.

[JS0711]

Thank you for the solution. I am only a newbie and having trouble with the Merge step. The data doesn't seem to populate as I hope and perhaps my wanted outcome has changed.

Data is below

I have successfully set up the calendar to show earliest to latest dates.

My merge function doesn't populate all dates ? Do I expand the calendar? 

My desired outcome is to show status types x 3 on each job at any month interval. ie

• Before StartDate = Status1
• Between SSDate and HODate = Status2 and
• After HODate = Status3

I will want to execute a count of each status and deliver a chart in power BI and also I will then want to access a list of the UDFCode (Job) so I can access some additional dat such a contracted value from another table

Thanks again for your help

[Anonymous]

Hi @JS0711 

I also offered a solution above, you can refer to it to see if it can help you.

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.

[JS0711]

Thank you for replying. Can your solution be adopted for my most recent update to do more than just a count of status?

[Anonymous]

Hi @JS0711 

Can you show us the renderings you want?

Best Regards!

Yolo Zhu

[JS0711]

Thank you,

I have three specific dates attached to each job:

1. Sale date
2. Start Date
3. Finish Date

At any time over history I want to be able to see the status of a job based on above dates eg

• Sold Not Started = Sale date but not Started Date
• Started Not Finished = Started Date but not Finished Date
• Finished = Finished Date entered

I will then want a stacked area chart showing the respective job statuses at any time over history as I slide across via a count

Also will want to able to have a list of the jobs and the associated contract value

Date Data 

Job and Contract value is held in another table