Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
G-Morse
Regular Visitor

Continuous Date Series

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 IDUnique Specification IDOccurredOUT Status Type
7-11694527-18358010/05/2023 00:00Active
7-11694767-18358010/05/2023 00:00Active
7-11695237-18358011/05/2023 00:00Active
7-11745237-18358001/06/2023 00:00Active
7-11748017-18358002/06/2023 00:00Active
7-11750177-18358002/06/2023 00:00Completed

 

Any help would be appriciated.

7 REPLIES 7
G-Morse
Regular Visitor

I need it to Count of Specification ID for each missing date between the dates available. so i can create a chart like this, Yellow ones being the missing Count. 

Example Dates.png

@G-Morse,

 

Try these measures:

 

Count ID =
CALCULATE (
    DISTINCTCOUNT ( 'Specification Status History_V'[Unique Specification ID] ),
    ALLSELECTED ( 'Specification Status History_V' )
)
Count ID Color =
IF (
    ISBLANK ( COUNT ( 'Specification Status History_V'[Unique Specification ID] ) ),
    "#E6811D",
    "#12239E"
)

 

Create a column chart with Count ID as the Y axis. In the Format Pane, go to Columns --> Color, and click the fx button.

 

DataInsights_0-1707156641691.png

 

Select the following:

 

DataInsights_1-1707156665368.png

 

Result:

 

DataInsights_2-1707156685934.png

 

-----

 

DataInsights_3-1707156701310.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




DataInsights
Super User
Super User

@G-Morse,

 

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.

 

Status Type = 
VAR vCurrentDate =
    MAX ( Dates[Date] )
VAR vPreviousDate =
    CALCULATE (
        MAX ( 'Specification Task History Status_V'[Occurred] ),
        Dates[Date] < 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

 

Use Dates[Date] in a visual.

 

DataInsights_0-1705509249778.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks @DataInsights 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? 

GMorse_0-1705518802129.png

 

@G-Morse,

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @DataInsights  here is my DAX & 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. 

 

Unique Specification IDOccurredOUT Status Type
7-18358010/05/2023 00:00Active
7-18358011/05/2023 00:00Active
7-18358001/06/2023 00:00Active
7-18358002/06/2023 00:00Active
7-18358110/05/2023 00:00Active
7-18358111/05/2023 00:00Active
7-18358112/05/2023 00:00Active

 

 

History Status Type = 
VAR vCurrentDate =
    MAX ( DateDimension[Date] )
VAR vPreviousDate =
    CALCULATE (
        MAX ( 'Specification Status History_V'[Occurred] ),
        DateDimension[Date] < 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

 

 

@G-Morse,

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors