Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Time Series data handling

Hi everyone, I'm currently trying to create a time series visual to analyze some processes trends. The topic here is that my data tables only have records where the process occurs, thus, I don't have data for the whole continous date range I want to display in my time series, along with this, since I don't have 0 values (records when the process didn't occurs) my trend never gets back to 0.

This is my data table:

It is calculated from my original data where I have records for several processes, only filtering those of my interest and their values as follows:

 

SummarizedBreaks = 
SUMMARIZE (
    FILTER (
        'Resultados Body',
        'Resultados Body'[Metrico] IN { "Break1", "Break2"}
    ),
    'Resultados Body'[Fecha],
    'Resultados Body'[Area],
    'Resultados Body'[Metrico],
    "SumOfValue", COALESCE(CALCULATE(SUM ( 'Resultados Body'[Valor] )), 0)
)

My time series is build like this:

but it only display the dates in the table. I already try creating a DateTable with the whole date range I'd like to show and relate the two tables by date column, but still the same even with "show elements without data" selected:

I'm trying to join the two tables to get a full date range table with null values which I can then convert to 0 and complete the graph but I can't get to the solution. 

 

 

Need Help
Tutorial Requests
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    Hi Anonymous ,

     

    To resolve the issue of missing dates in your time series visualization and ensure a continuous date range in your graph, you need to integrate your data with a properly structured date table. Start by creating a comprehensive Date Table that includes the entire range of dates you want to display. You can use the following DAX formula to create it:

    DateTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2025, 12, 31)), -- Adjust the date range as needed
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "Month Number", MONTH([Date]),
    "Weekday", FORMAT([Date], "dddd"),
    "Weekday Number", WEEKDAY([Date], 2)
)

    Make sure to replace the date range (DATE(2020, 1, 1) to DATE(2025, 12, 31)) with the desired range that aligns with your dataset. Once the Date Table is created, relate it to your data table by establishing a one-to-many relationship between the Date column in the DateTable and the Fecha column in your summarized data table.

    In your visualizations, use the Date column from the Date Table instead of the Fecha column from your original data. Also, ensure that the "Show items with no data" option is enabled for the date axis. To do this, select your visual, go to the Format pane, locate the X-axis settings, and enable "Show items with no data."

    Next, adjust your SummarizedBreaks table to include all dates from the Date Table, ensuring no dates are missed. You can use a combination of GENERATE and SUMMARIZE for this, as shown below:

    SummarizedBreaks = 
ADDCOLUMNS(
    GENERATE(
        DateTable, -- This ensures all dates are included
        SUMMARIZE(
            FILTER(
                'Resultados Body',
                'Resultados Body'[Metrico] IN { "Break1", "Break2" }
            ),
            'Resultados Body'[Fecha],
            'Resultados Body'[Area],
            'Resultados Body'[Metrico]
        )
    ),
    "SumOfValue", COALESCE(SUMX('Resultados Body', 'Resultados Body'[Valor]), 0)
)

    Additionally, to handle missing values and ensure that dates with no processes return a value of 0, modify your measure or calculated column to use the following DAX logic:

    SumOfValue = 
IF(
    ISBLANK(SUM('Resultados Body'[Valor])),
    0,
    SUM('Resultados Body'[Valor])
)

    Finally, test your visual by using the Date column from your Date Table on the X-axis and the updated SumOfValue measure on the Y-axis. This approach guarantees that your time series graph displays a continuous date range with 0 values for dates where no processes occur, providing a trendline that properly returns to 0 when necessary.

     

    Best regards,

1 Reply

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    Hi Anonymous ,

     

    To resolve the issue of missing dates in your time series visualization and ensure a continuous date range in your graph, you need to integrate your data with a properly structured date table. Start by creating a comprehensive Date Table that includes the entire range of dates you want to display. You can use the following DAX formula to create it:

    DateTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2025, 12, 31)), -- Adjust the date range as needed
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "Month Number", MONTH([Date]),
    "Weekday", FORMAT([Date], "dddd"),
    "Weekday Number", WEEKDAY([Date], 2)
)

    Make sure to replace the date range (DATE(2020, 1, 1) to DATE(2025, 12, 31)) with the desired range that aligns with your dataset. Once the Date Table is created, relate it to your data table by establishing a one-to-many relationship between the Date column in the DateTable and the Fecha column in your summarized data table.

    In your visualizations, use the Date column from the Date Table instead of the Fecha column from your original data. Also, ensure that the "Show items with no data" option is enabled for the date axis. To do this, select your visual, go to the Format pane, locate the X-axis settings, and enable "Show items with no data."

    Next, adjust your SummarizedBreaks table to include all dates from the Date Table, ensuring no dates are missed. You can use a combination of GENERATE and SUMMARIZE for this, as shown below:

    SummarizedBreaks = 
ADDCOLUMNS(
    GENERATE(
        DateTable, -- This ensures all dates are included
        SUMMARIZE(
            FILTER(
                'Resultados Body',
                'Resultados Body'[Metrico] IN { "Break1", "Break2" }
            ),
            'Resultados Body'[Fecha],
            'Resultados Body'[Area],
            'Resultados Body'[Metrico]
        )
    ),
    "SumOfValue", COALESCE(SUMX('Resultados Body', 'Resultados Body'[Valor]), 0)
)

    Additionally, to handle missing values and ensure that dates with no processes return a value of 0, modify your measure or calculated column to use the following DAX logic:

    SumOfValue = 
IF(
    ISBLANK(SUM('Resultados Body'[Valor])),
    0,
    SUM('Resultados Body'[Valor])
)

    Finally, test your visual by using the Date column from your Date Table on the X-axis and the updated SumOfValue measure on the Y-axis. This approach guarantees that your time series graph displays a continuous date range with 0 values for dates where no processes occur, providing a trendline that properly returns to 0 when necessary.

     

    Best regards,