Skip to main content
cancel
Showing results for 
Search instead 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

Reply
barn
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:

 

UserIdCreated_date
00108/11/2022
00212/11/2022
00312/11/2022
00415/11/2022
00516/11/2022
00622/11/2022
00724/11/2022

 

Thank you for any advice!

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
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.

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
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.
FreemanZ
Super User
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.

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:

barn_0-1669645546356.png

 

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

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

 

barn_0-1669650855682.png

 

May i see how User Count is defined?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors