cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SamFosterSCL Frequent Visitor
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

Accepted Solutions
Super User
Super User

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

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
5 REPLIES 5
Super User
Super User

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

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 .

Super User
Super User

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

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
SamFosterSCL Frequent Visitor
Frequent Visitor

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

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!

Super User
Super User

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

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

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

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!