Continuous Date Series

Question

In my Power BI model, I am working with a table named "Specification Task History Status_V." This table is designed to capture the historical movement of task statuses, specifically noting changes by the Occurred date. In some instances, a task status may remain constant for consecutive days, leading to a gap in the occurred dates.

For example, between 11/05/2023 and 01/06/2023, there might not be recorded dates for status changes. However, it is crucial to represent this period accurately in visualizations so showing a Count as 1 for days 12th, 13th and so on. To achieve this, I need to create a graph where the status remains constant and is counted for each day within the specified date range, even though there may not be explicit entries for those dates in the dataset. Essentially,

I am seeking a solution that ensures the continuous representation of a status during periods with no recorded changes, counting it as 1 for each day within the given range. This adjustment is vital for creating a comprehensive and accurate graphical representation of the task status progression. Here is an example of my data for one Specification ID:

Unique Specification History ID	Unique Specification ID	Occurred	OUT Status Type
7-1169452	7-183580	10/05/2023 00:00	Active
7-1169476	7-183580	10/05/2023 00:00	Active
7-1169523	7-183580	11/05/2023 00:00	Active
7-1174523	7-183580	01/06/2023 00:00	Active
7-1174801	7-183580	02/06/2023 00:00	Active
7-1175017	7-183580	02/06/2023 00:00	Completed

Any help would be appriciated.

datainsights · Answer

G-Morse,
&nbsp;
Try this measure. It requires a date table Dates with a relationship to the fact table. You can adapt this pattern to return a count.
&nbsp;
Status Type = 
VAR vCurrentDate =
    MAX ( Dates[Date] )
VAR vPreviousDate =
    CALCULATE (
        MAX ( 'Specification Task History Status_V'[Occurred] ),
        Dates[Date] &lt; vCurrentDate
    )
VAR vFilterPreviousDate =
    TREATAS ( { vPreviousDate }, Dates[Date] )
VAR vStatusTypePreviousDate =
    CALCULATE (
        MAX ( 'Specification Task History Status_V'[OUT Status Type] ),
        vFilterPreviousDate
    )
VAR vResult =
    IF (
        // if Dates[Date] does not exist in fact table, use the last value
        ISEMPTY ( 'Specification Task History Status_V' ),
        vStatusTypePreviousDate,
        MAX ( 'Specification Task History Status_V'[OUT Status Type] )
    )
RETURN
    vResult
&nbsp;
Use Dates[Date] in a visual.
&nbsp;

&nbsp;

g-morse · Answer

Thanks&nbsp;DataInsights&nbsp;I think this helps me get half way there, I created the table on the right as you said, how can I get it to show a value in Specfication ID so I can show this on the chart each day?&nbsp;&nbsp;

datainsights · Answer

G-Morse,
&nbsp;
Would you provide your DAX and sample data (table format or pbix) that will enable me to replicate your screenshot? It appears the issue is with the measure returning blank for most of the rows.

g-morse · Answer

Hi&nbsp;DataInsights&nbsp; here is my DAX &amp; Table, please bare in mind this is only filtered to one record (Unique Specificartion ID) I will be eventually un-restricting this for all of those rows, therefore I have included two Specification ID's for you to test.&nbsp;&nbsp;Unique Specification IDOccurredOUT Status Type7-18358010/05/2023 00:00Active7-18358011/05/2023 00:00Active7-18358001/06/2023 00:00Active7-18358002/06/2023 00:00Active7-18358110/05/2023 00:00Active7-18358111/05/2023 00:00Active7-18358112/05/2023 00:00Active&nbsp;&nbsp;History Status Type = 
VAR vCurrentDate =
    MAX ( DateDimension[Date] )
VAR vPreviousDate =
    CALCULATE (
        MAX ( 'Specification Status History_V'[Occurred] ),
        DateDimension[Date] &lt; vCurrentDate
    )
VAR vFilterPreviousDate =
    TREATAS ( { vPreviousDate }, DateDimension[Date] )
VAR vStatusTypePreviousDate =
    CALCULATE (
        MAX ( 'Specification Status History_V'[OUT Status Type] ),
        vFilterPreviousDate
    )
VAR vResult =
    IF (
        // if Dates[Date] does not exist in fact table, use the last value
        ISEMPTY ( 'Specification Status History_V' ),
        vStatusTypePreviousDate,
        MAX ( 'Specification Status History_V'[OUT Status Type] )
    )
RETURN
    vResult&nbsp;&nbsp;

datainsights · Answer

G-Morse,
&nbsp;
Is the objective to get a row for each date when using the measure History Status Type, or the measure Count of Specification ID in your screenshot? If you could create a mockup of the expected result it would be helpful.