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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SamFosterSCL
Frequent Visitor

Get all the dates in the date range to display 1-31

When working with dates on a chart, I want it to display all the possible dates and not just the continous option it gives you.

 

I need it to display the dates of a month 1-31, and show all of them on the axis. 

 

Does anyone know how this is possible?

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey,

 

I'm assuming that you have a calendar table where a column shows the number of the day in a specific month, the distinct range are the values from 1 to 31

 

First make sure that you have configured the x-axis in your visualization ...

 

categorical x-axis.png

 

Then make sure the option "Show items with no data" is enabled ...

 

Show Items with no data.png

 

And this be a resulting report ...

 

report - items with no data are shown.png

 

Please be aware that months that are used in a slicer, with less than 31 days just show the corresponding number of days in that month.

 

Hope this helps



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

5 REPLIES 5
TomMartens
Super User
Super User

Hey,

 

I'm assuming that you have a calendar table where a column shows the number of the day in a specific month, the distinct range are the values from 1 to 31

 

First make sure that you have configured the x-axis in your visualization ...

 

categorical x-axis.png

 

Then make sure the option "Show items with no data" is enabled ...

 

Show Items with no data.png

 

And this be a resulting report ...

 

report - items with no data are shown.png

 

Please be aware that months that are used in a slicer, with less than 31 days just show the corresponding number of days in that month.

 

Hope this helps



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thank you! This is exactly what I needed!

 

Now, if I wanted this in a line graph and to show a constant line throughout the chart showing when it hits 0 and going back up when there is data in there how do I do it?

 

Many thanks again!

This is a more general reply to your request of a constant line and maybe this does not help in your current situation.

 

The visualization of BLANK (missing) values is different from visualizing 0 (zero) values.

 

Here an example for missing values

I'm (as a customer) not buying a product on each day. This is reflected in a column chart by missing columns. I do not buy zero coffees.

 

Here is an example for zero values

I'm measuring defects, gladly there are days with no defects (assuming that the product in mind, is produced each day), now it makes sense so to use a line chart to show the defects.

 

If you come to the conclusion that my distinction between missing and zero values is just splitting of hairs - please excuse 🙂

 

Cheers,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hey Sam,

 

unfortunately there is no "property" for the line chart viz, that helps to achieve what you want.

 

But maybe you can use a calculated measure like this

 

SalesAmountBlankToZero = calculate(if(ISBLANK(sum('factinternetsales'[salesamount]));0;sum('factinternetsales'[salesamount])))

Here I check if a certain column/measure (this is the problem, a certain column/measure has to be used) has a value using the function ISBLANK. If the value is BLANK the I set this to 0 (zero).

 

This approach has some shortcomings:

  • a certain measure has to be used (due to the lack of the "CURRENT MEMBER" concept in DAX in comparison to MDX)
  • for february the 30th and 31th day will always be BLANK and the 29th most of the time

An R graph could be helpful, but this would also mean that your users of the report/dashboard have to have a PRO licence.

 

Hope this helps



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Baskar
Resident Rockstar
Resident Rockstar

cool, 

 

1. If u have all date in your table we can show like this.

 

if u don't have ,

 

create new calculated table using CALENDER ( Start_Date,End_Date), then link the tables .

 

Use date from this calculated table. this should work .

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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