Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.