Forum Discussion
Dates on a graph - continuous -adding a date dimension
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
AFTER
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,
Did you use the new table for your axis?
Another thing to try, go into the PaintBrush section of the graph visual and open up "X-Axis". Try changing the type to continuous.
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
AFTER