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
ozmike
Resolver I
Resolver I

Dates on a graph - continuous -adding a date dimension

Hi

 

I have graph with 'item create date' on the y - axis ,and count of the  items on the X - Looks ok except it never shows any 0 values - I guess because there are days that nothing was created. Ie count(Item ID) will be 1 or > 1 on any created date never 0 , a count value is only shown for dates that have data. So how do we show the gaps to be zero..

 

In MSaccess you would join to a table with every date in the calender..a bit cumbersome..

 

but is there  a setting to report gaps..

 

1 ACCEPTED SOLUTION

OK solved 

 

- what are we trying to achieve..show a value on a graph of zero, for days that have no data or events.

 

How to achieve ..basically ..create a list of all calender dates for the period of data in question and the left join that to your data

  

Note, there is a power query M function to create a list of dates List.Dates ( date dimension)

Note, start DATE in this case 2015,12,7,  Determine your earliest date in your data.

This will create a list of dates with no future dates !

 

Step 1

in The query Editor create a blank query

and paste the below into the advanced editor.

 

let
    Source = #date(2015,12,7),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Date", each List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Date", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Start Date"}})
in
    #"Renamed Columns"

 

 

2) Query Editor create another query as  the left outer join (merge ) your "Date" list  column with a date in your data which has no data on some days.

 

3) Query editor create a custom column called 'Data Exists' with this formula, where ID is column can be null on some dates.

if [ID] is null then 0  else 1

 

4) change type to whole number. Must be a number or it dosen't work.

 

5) create a graph put "date" on the x-axis.

 

6)  the column 'Data Exists' should have a sigma sign on meaning it is a number. Drag on the value axis and it will sum these. 

 

Graph will now go to ZERO where there are gaps - SIMPLE ?

 

 

BEFORE

Capture.PNG

 AFTER

Capture.PNG

 

View solution in original post

22 REPLIES 22
Gnanasekar
Helper III
Helper III

Hi @ozmike

 

Go visual filter in that graph.

Select dropdown in Item ID. Choose Advance filtering there, you select 'is greater than' and give '0' in below box. (Like in image) and click apply filter. 

pastedImage.png

Now you give will get value above zero.

 

 

By

Gnanasekar

 

I tried the suggestion. However  I have no problem displaying values greater than 0  ! The issue is how do you show 0 in a continious graph! How to show 0 in the gaps!  Some days there was no event - no rows - no data - how to show 0 when no data.

Hi @ozmike

 

Can You try with below measure 

 

Item = if(Item ID >0,Item ID,0)

 

if no data in Item ID, you will get zero there. if data present, you will get same data there.

 

Now graph will be in continious.

 

By

Gnanasekar

Anonymous
Not applicable

What about creating a Date Dimension table and linking that table to the table you are presently graphing?  If you make the Date Table you axis, you will show 0s for the dates that have no linked data rows.

Ross73312       

 

Date Dimension table - Do you mean create a table of linear calender dates ?   How would you do this. In excel you enter a date and drag column down and dump to a file but , this is alway problematic and how far into the futrue do you go.. or is there another way..in power bi ..ie create a date demension table - button..

Anonymous
Not applicable

Sure can!  Have a read of this article.

http://www.agilebi.com.au/power-bi-date-dimension/

Look promising! I will try it out next week unfortunately i have to go and if it flys you'll get credit..!

Thanks got the list of date now what? any ideas..?

 

Hi I was able to invoke the function to create the list of dates. I tried to do an outer join from the date list to the data . seems to work. I have a data in the 2030s in the data , but if I do a measure in the visual and do a max I only get 2017 where the data ends.

Also if I do a graph dosen't show any data in the 2030s..not sure where to go form here...

 

 

Anonymous
Not applicable

You could create a calculated column in your new date table, that is a simple On/Off switch for future dates.  Something like:

isFuture = IF(
	[Date] > TODAY(),
	TRUE(),
	FALSE()
)

 

You could add a filter into your report filters that only shows when isFuture = False

That function is great idea ! but thats not the problem i'm getting - i'm not seeing any future dates so the graph is also not showing any zero values,

 Capture.PNG

 

Hi yes I rebuilt the graph a number of times from scratch , won't show future values..then data is in there also , i can see future data in the table visual and built a slicer based on your 'isfuture' ..seems to work but won't show in graph...i'll have to get back to you on this one..

Graph is continious too..

I have discovered the issue but can't work around it. The continious date field is type text when M function creates the table.

And then you left join that data  to the real data. You look in the visual graph its there ..as text type not date , so the graph is out of order as it is text sorting, So you change the column type to Date and graph only show the inner join data..

 

still working on it..

OK solved 

 

- what are we trying to achieve..show a value on a graph of zero, for days that have no data or events.

 

How to achieve ..basically ..create a list of all calender dates for the period of data in question and the left join that to your data

  

Note, there is a power query M function to create a list of dates List.Dates ( date dimension)

Note, start DATE in this case 2015,12,7,  Determine your earliest date in your data.

This will create a list of dates with no future dates !

 

Step 1

in The query Editor create a blank query

and paste the below into the advanced editor.

 

let
    Source = #date(2015,12,7),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Date", each List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Date", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Start Date"}})
in
    #"Renamed Columns"

 

 

2) Query Editor create another query as  the left outer join (merge ) your "Date" list  column with a date in your data which has no data on some days.

 

3) Query editor create a custom column called 'Data Exists' with this formula, where ID is column can be null on some dates.

if [ID] is null then 0  else 1

 

4) change type to whole number. Must be a number or it dosen't work.

 

5) create a graph put "date" on the x-axis.

 

6)  the column 'Data Exists' should have a sigma sign on meaning it is a number. Drag on the value axis and it will sum these. 

 

Graph will now go to ZERO where there are gaps - SIMPLE ?

 

 

BEFORE

Capture.PNG

 AFTER

Capture.PNG

 

Anonymous
Not applicable

Did you use the new table for your axis?

Anonymous
Not applicable

Another thing to try, go into the PaintBrush section of the graph visual and open up "X-Axis".  Try changing the type to continuous.

ozmike
Resolver I
Resolver I

Capture.PNG

 

Hi I want the line to go to 0 on days where nothing happened..( there is no data for these days). at the moment it jumps to the next 1.

Hi @ozmike,

 

  are your dates & the qty continuous ? in other words make sure on no sale day you have "0" as an entry in your qty column corresponding to a valid continuous date. 

 

Regards,

Yes I want a zero , on a day with no sale , but as there is no data for that date because there was no sale  how do I show missing dates ?

I probably feel that you need to spend some time in remodelling of your data. Since a line graph is nothing but a line that connects point so data have to have a "0" entry & a continuous date to keep the line graph continuos. This is the best solution i have at this moment with me. May be others can have a better solution. you can surely wait for them too.

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.