Timeline chart view Date when timestamp is empty

[AgoZ_KH]

Hi community! Have a fun and nice day!

I'm facing with this issue.

I have an input data file with daily measure. Sometimes the daily misure are missing. So when I plot the timeline chart same days aren't displayed. Suppose you haven't measure between 5 and 9 february my timeline went: 1/2/3/4/10/11/12 and so on.

My problem is to plot every day in the week also if the timestamp is empty (i.e. measure are 0).

Try to make a calendar table and creating logical function to manage the problem, without success! Can onyone help me?

Thanks in advance!

[Anonymous]

Would your problem be solved if you add +0 to the end of your measure? 

[AgoZ_KH]

For sure  I didn't make myself clear!!

My data (measures) are collected in a DB from an external source. If source have measures in the day these are registred on the DB with the datastamp (i.e. 02/22/2020 14:30) if not simply there is no data on the DB.

I cant'control this and put blank data in the DB if there are not measures for a specific day.

Thanks you!

[Icey]

Hi @AgoZ_KH ,

Which type of visual are you using? Can the type of X-axis be set as "Continuous" instead of "Categorical"?

In addition, you can try to create a new measure in Power BI like below to meet your requirements:

New Measure in Power BI = 
SUM ( 'YourTable'[Your old measure] ) + 0  //If your old measure has become a column in Power BI.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AgoZ_KH]

Hi @Icey ! 

I discover, by your posted image, I didn't have the last version of Desktop pBI!! So I download it. But I don't have anyway the choice to set X-Axis. I use the object Graph Line. Which kind of grafic are you using?

Best regards

A.

[Icey]

Hi @AgoZ_KH ,

I just use the simplest "Line chart" as an example. 

And I don't find the visual "Graph Line" you are using.

In addition, which field do you use as "X axis"? Could you create a sample file for test? Please don't contain any sensitive information.

Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Icey

[AgoZ_KH]

Hi Icey! 

the chart is the same.

Here as requested an example of my report and data model (simplificated):

https://www.dropbox.com/s/7j53vb4cl2na467/report_example_agoz.pbix?dl=0

Note that between the 4th and 9th february I have no data. My goal is to show this days (of cours with 0 data).

Hope this clarify my issue.

Best regards

AgoZ

[Icey]

Hi @AgoZ_KH ,

In your scenario, it is needed to create a calendar table like so:

Calendar =
ADDCOLUMNS ( CALENDARAUTO (), "Week", WEEKNUM ( [Date], 2 ) )

Then create a Date column in your data table:

operation_date = [operation_timestamp].[Date]

Next step is to create a relationship between the two tables:

Then we can create measure to add "+0" to meet your requirements:

Max = 
MAX ( last_six_month_history[Measure A] ) + 0

Min = 
MIN ( last_six_month_history[Measure A] ) + 0

Med = 
AVERAGE ( last_six_month_history[Measure A] ) + 0

Use "Date" and "Week" column from "Calendar" table and here is the result

We can find that, there is also data before 2022/1/9. To prevent it, modify the measures like so:

Modified - Max = 
VAR MaxDate_ =
    CALCULATE (
        MAX ( last_six_month_history[operation_date] ),
        ALLSELECTED ( 'Calendar' )
    )
VAR MinDate_ =
    CALCULATE (
        MIN ( last_six_month_history[operation_date] ),
        ALLSELECTED ( 'Calendar' )
    )
VAR CurDate_ =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        CurDate_ >= MinDate_
            && CurDate_ <= MaxDate_,
        MAX ( last_six_month_history[Measure A] ) + 0
    )

Modified - Min = 
VAR MaxDate_ =
    CALCULATE (
        MAX ( last_six_month_history[operation_date] ),
        ALLSELECTED ( 'Calendar' )
    )
VAR MinDate_ =
    CALCULATE (
        MIN ( last_six_month_history[operation_date] ),
        ALLSELECTED ( 'Calendar' )
    )
VAR CurDate_ =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        CurDate_ >= MinDate_
            && CurDate_ <= MaxDate_,
        MIN ( last_six_month_history[Measure A] ) + 0
    )

Modified - Med = 
VAR MaxDate_ =
    CALCULATE (
        MAX ( last_six_month_history[operation_date] ),
        ALLSELECTED ( 'Calendar' )
    )
VAR MinDate_ =
    CALCULATE (
        MIN ( last_six_month_history[operation_date] ),
        ALLSELECTED ( 'Calendar' )
    )
VAR CurDate_ =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        CurDate_ >= MinDate_
            && CurDate_ <= MaxDate_,
        AVERAGE ( last_six_month_history[Measure A] ) + 0
    )

And the result is below:

For more details, please check the attached .pbix file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.