Show dates with no values in line chart

[barn]

Hi!

I am creating a line chart that shows me how many users have signed up per date.

My dataset is simply the list of users and their account creation date. However, some dates there were zero new users. I would like to include those dates too, but as zeros on the chart.

Example dataset:

UserId	Created_date
001	08/11/2022
002	12/11/2022
003	12/11/2022
004	15/11/2022
005	16/11/2022
006	22/11/2022
007	24/11/2022

Thank you for any advice!

[Anonymous · Accepted solution]

Hi @barn,

You can try to create a calendar table with full date value and use it as axis on your chart.

Then you can write a measure formula to count users based on current date, and add +0 to the end of expression to auto replace the blank result to zero. 

formula =
VAR currDate =
    MAX ( Calendar[Date] )
RETURN
    CALCULATE (
        COUNTA ( Table[UserId] ),
        FILTER ( ALLSELECTED ( Table ), [Created_date] = currDate )
    ) + 0

After these steps, you can use this format on chart as value to finish the design of chart graph.

Regards,

Xiaoxin Sheng

[FreemanZ]

It shall be achieveable with this:

1) create a Date table,

2) link Date[Date] with Ceated_date column.

3) pull the Date[Date] column in the X-axis in your chart visual. 

then you will see what you expect.

[barn]

By "link Date[Date] with Ceated_date column", do you mean in the model relationship manager?

I've got my date table, and have created the relationship, but it doesn't seem to pull through when I try to add it to the x-axis.

Screenshot:

[FreemanZ]

what do you mean by pull through? a screenshot would be good. 

[barn]

Sorry, I just mean it doesn't display on my chart

[FreemanZ]

May i see how User Count is defined?