Forum Discussion

somulaj's avatar
somulaj
Regular Visitor
5 years ago
Solved

forcing zeros to draw a continuous line

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 grap...
Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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:

     

    1. Create a measure in the main table:
    Sales value =

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

return

IF(_sum=BLANK(),0,_sum)
    1. 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.

HashamNiaz's avatar
HashamNiaz
Icon for Solution Sage rankSolution Sage
5 years ago

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's avatar
somulaj
Regular Visitor
5 years ago

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

  • HashamNiaz's avatar
    HashamNiaz
    Icon for Solution Sage rankSolution Sage
    5 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    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.