line chart not representing dates without data

[Alberto76]

Hi all,

I've searched through the posts and around internet but I didn't found a similar problem like mine solved.

I have a DB containing IDs of services and related quantities over months and years (so there is a column with dates), and I have created a measure to calculate running totals with this formula:

= CALCULATE(
     SUM(DB[Qty]);
     FILTER(
        CALCULATETABLE(
            DB;
            ALL(DB[Year_Month]);
            ALL(DB[ID]));
     DB[Year_Month]
         <= MAX ( DB[Year_Month] )
 ))

Some IDs have no quantities on every month (e.g. service with ID 1 have requests for Jan 2016 (+4), Mar 2016 (+1), Apr 2016 (+2) and Aug 2016(+3)).

When I create a line chart and I put the running total formula as y axis, the dates column as x axis and I filter on that ID, the line chart represent a continuous line with only 4 dots, in corrispondence to the 4 dates with values, but I would like to see all the dates from Jan to Oct, representing for dates without data the last total (e.g. Jan 4 -> Feb 4 -> Mar 5 (4+1) -> Apr 7 (5+2) ->May 7 -> Jun 7 -> Jul 7 -> Aug 10 (7+3).

Enabling the "Show items without data" do not change anything.

I also tried to create a Date DB, using the CALENDAR formula, and use the dates column of this DB as x axis, but the problem persists (represented dot are always the one with data only), moreover the running total formula do not work anymore, as the line represent the absolute value for the date and not the running total up to that date.

I would like not to create "artificial" records with 0 value for the months that have no data, because the DB already is over 100k records.

Have you any suggestion on how to manage this problem?

Thank you.
Alberto

[v-yulgu-msft · Accepted solution]

Hi @Alberto76,

If there are no data records in a month,  the line chart will not represent dots for those month. In my test, I created a calendar table and crossjoin it with the DB table. However, I think this workaround is not appropriate to the dataset which contains very large records.

In this crossjoin table, I added a calculated column:

Column = IF('Table 5'[Year_Month]='Table 5'[Date],'Table 5'[Qty],'Table 5'[QTY2])

New another calculate table only contains ID, Year_Month and Qty columns:

Table 6 = SELECTCOLUMNS('Table 5',"ID",'Table 5'[ID],"Date",'Table 5'[Date],"QTY",'Table 5'[Column])

Then, I put the running total measure in Value, the dates column in Axis. I got the below output, the line chart represents a continuous line with dots for everyday.

Thanks,
Yuliana Gu

[Alberto76]

Hi Youliana,

sorry, the solution was quite simple: using CROSSJOIN formula...

Thank you again!

Best regards

Alberto

[Alberto76]

Hi Yuliana,

sorry for my late feedback but I had some emergency to menage...

I conceptually understood the logic you applied, but, as I'm a newby of Power BI, I didn't catch the way you performed the crossjoin between the tables, if you have done it using the "Merge query" tool or via DAX formula, or doing steps in both.

May you please clarify this?

Thanks

Alberto