cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Show dates with no values in line chart

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

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
6 REPLIES 6
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User

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.

New Member

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:

Super User

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

New Member

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

Super User

May i see how User Count is defined?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.