forcing zeros to draw a continuous line

[somulaj]

Hi,

I have Date and Sales columns in the database. I would like to see how my sales are changing by month using a line graph.

There is no entry for April 2020 in the database. So in my line graph, i do see continous graph till march 2020 and agian line will start from May 2020 and the continuity is missing for the month April 2020.

Is there any way I can force the line for April 2020 with 0 sales. 

Thanks,

Jay

[v-robertq-msft · Accepted solution]

Hi, @somulaj 

According to your description, I can understand your problem, I think you can solve this problem using a calendar table and a measure, you can follow my steps:

This is my test data:

1. Create a calendar table a calculated column in the table:

Date = CALENDAR(DATE(2021,1,1),DATE(2021,8,31))

Year month = YEAR([Date])&"-"&MONTH([Date])

Then give them a relationship with the date column:

2. Create a measure in the main table:

Sales value =

var _sum=SUM('Table'[Sales])

return

IF(_sum=BLANK(),0,_sum)

3. Create a line chart and place it like this:

And you can get what you want.

You can download my test pbix file below

If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[somulaj]

Thank you. Its worked

[HashamNiaz]

Hi @somulaj !

You can write your DAX measure as following;

Sales1 = IF(ISBLANK([Sales], 0, [Sales])

You can replace [Sales] measure with your measure calculation. Hope this will solve.

Regards,

Hasham

[somulaj]

Hi Hasham,

If I have an entry for April 2020 in my database with blank sales then I can write DAX. My question is how do we get April 2020 Date in the database. 

Thnaks,

Jay

[Ashish_Mathur]

Hi,

Ensure you have a Calendar Table.  Build a relationship from the Date column of your Table to the Date column of your Calendar Table.  In the Calendar Table, write calculated column formulas to extract Year, Month name and Month number.  Sort the Month name column by the Month number.  To your visual, drag Year and Month name column from the Calendar Table.  Write this measure

Sales = coalesce(sum(data[amount]),0)

Hope this helps.

[HashamNiaz]

Hi @somulaj !

You are using Calendar / Date dimension in your Data Model. You data model Calendar dimension should have all the dates irrespective of your sales.

You can plot the sales again Calendar dimension date, and you can set the property to show items with no data.

You can create a basic Calendar table using below DAX; go to Modeling -> New Table;

Calendar = CALENDARAUTO()

Then connect this table with your Fact (Sales) table using Date key.

Regards,

Hasham