Solved: Re: Dates on a graph - continuous - Page 2

ozmike · ‎05-16-2017

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..

ozmike · ‎05-22-2017

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

View solution in original post

ozmike · ‎05-16-2017

Yes one work around is to have a table with all dates in the calender (cartesian join - no join - don't know if you can do it in PBI) , so you can get these zero values in the data set.

But I thought by now (2017) - date range woudl be easy - Interesting it streches/scales the y axis where there is no date data (good) but shows a 1 ( actually there is nothing there - i think excel and msaccess did the same). but as you say it just joins the dots...maybe a scatter plot is the work around..

hemantsingh · ‎05-16-2017

hi @ozmike,

Can you share some screenshot for better understanding of the issue.

Regards,

Hemant

Dates on a graph - continuous -adding a date dimension

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly