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

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.

6 Replies

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    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.

    • somulaj's avatar
      somulaj
      Regular Visitor
      5 years ago

      Thank you. Its worked

  • 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