cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YRevolte
Frequent Visitor

Make appear 0 instead of Blank

Hello all,

Below a simplified view of my use case :

YRevolte_0-1625217697302.png

 

TIME.Date = FACTS.Creation date (active relationship)

TIME.Date = FACTS.Closure date (inactive relationship)

TIME is a date table, a reference table :

YRevolte_1-1625217697309.png

 

FACTS is my facts table where all the orders are registered.

YRevolte_2-1625217697311.png

 

I would like to make appear in the same chart the quantity created and closed during the time.

I’ve created these measures :

Created orders = SUM(FACTS[Quantity])

 

Closed orders = CALCULATE(SUM(FACTS[Quantity]),USERELATIONSHIP(FACTS[Closure date],'TIME'[Date]))

 

By these measures I have this result :

YRevolte_3-1625217697314.png

 

With this solution, there are BLANK instead of 0 for March (for example).

So, I’ve added “+0” to the measures but futures months are now valued…

 

YRevolte_4-1625217697322.png

 

Do you know a solution permit to make appear 0 for the past months and Blank for the future months ?

Thank in advance for your help... 🙂

1 ACCEPTED SOLUTION

@YRevolte 

updated the pbix file,pls see the attachment below.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Just ensure that the Date table only goes uptil the last date that appears in the FACTS table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

Thank you for your response.

But I would like to make appear the future months in the graph, not valued.

Hi,

Why do you want to show future months in the graph when there is no data for those future months.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Cause this is my need 😅

ryan_mayu
Super User
Super User

@YRevolte

here is a workaround for you.

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi,

Which version of PowerBi do you use ?

I have this result on your report :

YRevolte_0-1625733455074.png

0 is missing for March, April and May.

 

@YRevolte 

updated the pbix file,pls see the attachment below.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!