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
windynan
Advocate I
Advocate I

Show 0 when there is no data for certain date on the stacked area chart

I am trying to create a stacked area chart but I don't have the data for certain dates. Meaning there's no rows associated with certain date, it's not the case that there are rows with null or 0. I am trying to show those missing data as 0. Any idean how to do that? I tried the formula to do a sum then +0, and it didn't work. I am also trying to aviod the way of creating a new time table( because I already created one in the model and it can't be used for this visual). Any idean how to do this with just DAX formula?

Here are the data:

 

TimeGroupSales
2021-07-15 0:00A4003.884067
2021-07-31 0:00A6020.133462
2021-07-31 0:00B792.3333333
2021-07-31 0:00C1483.950956
2021-08-15 0:00A4275.699444
2021-08-15 0:00B542.3333333
2021-08-15 0:00C365.5340079
2021-08-31 0:00A6240.695745
2021-08-31 0:00B542.3333333
2021-08-31 0:00C1557.116907
2021-09-15 0:00A3040.167482
2021-09-15 0:00B542.3333333
2021-09-15 0:00C593.8743489
2021-09-30 0:00A819.5815928
2021-09-30 0:00B542.3333333
2021-09-30 0:00C448.5754824

 

I am trying to achieve below, have area filled under the two arrows. 

 

2021-06-29_9-55-34.png

 

 

 

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

Hey @windynan ,

 

the problem is not the measure, the problem is the data model.

I would highly recommend to create a dimensional date table, connect it to your fact table (the sample table you posted) and use that date table for your visuals. Then you won't have gaps and also the time intelligence functions will work.

 

Take a look on how to do that:

Power Bi for Beginners: How to create a Date Table in Power Bi - Softcrylic

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

2 REPLIES 2
selimovd
Super User
Super User

Hey @windynan ,

 

the problem is not the measure, the problem is the data model.

I would highly recommend to create a dimensional date table, connect it to your fact table (the sample table you posted) and use that date table for your visuals. Then you won't have gaps and also the time intelligence functions will work.

 

Take a look on how to do that:

Power Bi for Beginners: How to create a Date Table in Power Bi - Softcrylic

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Thanks @selimovd. I guess this is a way to do it. I ended up creating a new column in my original time table. It works. thanks!

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.