How to get horizontal lines instead of diagonal lines in my line graph

[Coen_Thönissen]

Hi there, here is my problem: on the x-axis I have 60 calendar days. On the y-axis I have the quantity of items in stock. That is a measure, a calculated DAX-formula reflecting purchases (number of inbound goods) and sales (number of outbound goods). Many days have no activity. It is a quite business.

Say on day_10 there have been some incoming goods, resulting in a number of items in stock of in total 80. The next activity is 6 days later, on day_16, with 24 items outgoing. That makes that day_16 will end with a number of items in stock of in total 56 (as 80 - 24 = 56). So far so good.

The problem is that my chart starts decreasing directly after day_10, with -4 per day. The line in the graph diagonally decreases during in 6 days in a row, to end up exactly at 56. So the starting point is correct (80), the end point is correct (56), but the line should of course remain horizontally at 80 during day_11, day_12, day_13, day_14 and day_15 and then finally drop stepwise by 24 on day-16.

How do I get a horizontal stepwise line in my graph, instead of diagonal lines?

Kind regards, Coen Thönissen

[v-eqin-msft]

Hi @Coen_Thönissen ,

According to my understand, you want to achieve horizontal changes instead of diagonal changes between two non-empty values, right?

You could follow these steps:

1. Create a calendar table:

Table 2 =
CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) )

2.Get the value from original table:

value =
LOOKUPVALUE ( 'Table'[Change], 'Table'[Date], 'Table 2'[Date] )

3. "Fill down" values in step2:

fill down =
VAR LastNonBlankDate =
    CALCULATE (
        LASTNONBLANK ( 'Table 2'[Date], 1 ),
        FILTER (
            ALL ( 'Table 2' ),
            'Table 2'[Date] <= EARLIER ( 'Table 2'[Date] )
                && NOT ( ISBLANK ( 'Table 2'[value] ) )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table 2'[value] ),
        FILTER ( ALL ( 'Table 2' ), 'Table 2'[Date] = LastNonBlankDate )
    )

My visualization looks like:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[dedelman_clng]

Hi @Coen_Thönissen  -

On the format settings for a line chart, look under the "Shapes" section and at the bottom you should find "Stepped On/Off"

Hope this helps

David

[Coen_Thönissen]

Hi David,

Thanks for helping me. Your solution however introduces a new problem, as the step is made not on the final day (day_16 in my example, which would be correct), not on the first day (day_10 in my example, which would be incorrect), but visually exactly in the middle (at day_13, which is incorrect). Can you suggest a solution to that effect?

Kind regards, Coen

[dedelman_clng]

@Coen_Thönissen - for that my suggestion would be to make sure the measure calculates and gives the previous value if there are no transactions for that day. Then with stepped on you should get the drop/rise directly before the next day with transactions.

If you don't know how to make the DAX work, please share a copy of your PBIX or sample data, model design and measure code.

David

[Coen_Thönissen]

Hi David,

Thanks again. Here some more background:

Sourcefile and in the PBIX-model simply called 'Table':

Date	Change
2020-03-01	30
2020-03-10	50
2020-03-16	-24
2020-04-05	12
2020-04-23	-60

Measure:

Items_in_stock = CALCULATE(SUM('Table'[Change]);FILTER(ALL('Table');'Table'[Date]<=MAX('Table'[Date])))

As mentioned before, the line graph I get either diagonally increases or decrease from preceding date to subsequent date or - when 'interval' is chosen - increases or decreases stepwise exactly in the middle between these dates. I want the step to occur on the subsequent date (being the date of the subsequent change).

I think the prrblem is that the date-column is not a 'linear axis', but is full of 'gaps'. But Power BI should be smart enough to deal with this, I think. Although my example is very simple, this is a frequently occuring issue in the real world.

Kind regards, Coen

[dedelman_clng]

Hi @Coen_Thönissen  - 

What you are looking to do is Time Intelligence, which requires a Calendar/Date table to be done correctly.  You can create a date table in a couple of ways

DateTab = CALENDARAUTO()

DateTab = CALENDAR("1/1/2020", "12/31/2020")

You then want to make a 1-to-many relationship between DateTab and your Table, via Table[Date]

Replace Table[Date] in your measure with DateTab[Date]

Items_in_stock =
CALCULATE (
    SUM ( 'Table'[Change] ),
    FILTER ( ALL ( 'DateTab' ), DateTab[Date] <= MAX ( DateTab[Date] ) )
)

Finally, use DateTab[Date] as your X-axis. Be sure the axis is set to Continuous

Hope this helps

David