Create a STEP CHART using PowerPivot

Question

_{Monday, May 4, 2015 11:05 AM}

Hi, 

I want to display prices using a step chart (Y=Price, X=Date).

The reason I need Step Chart and not a regular line chart is because the prices remain the same till new update arrives. 

Anyone did it?

I saw explanations how doing it on the web, but all of them used a predefined range to manipulate the data. I need something dynamic to fit the changes in the data (such as when using slicers to filter the results)

THX

עמית

All replies (8)

_{Monday, May 4, 2015 3:28 PM ✅Answered | 1 vote}

Hi,

I think you'll probably need a measure that gives the latest price for every choosen date:

LastPrice:=CALCULATE (

     SUM(Table[Value]),

     FILTER (Table,

         Table[Date]

             = CALCULATE (

                 LASTNONBLANK ( Table[Date], 1 ),

                 FILTER (

                     Table,

                     Table[Date] <= LASTDATE(DimDate[Date])))))

It is crucial that the DimDate-table is not connected to the Table-table! You need to drag your values for the x-axis from that DimDate table.

Imke

---

_{Sunday, May 17, 2015 7:16 PM}

Thx a lot for your reply. 

I wonder if you have a workbook you can attach to view the solution?

It will be a great help :)

עמית

---

_{Sunday, May 17, 2015 8:32 PM}

Hi Amit,

have a look: https://onedrive.live.com/edit.aspx?cid=DE165DDF5D02DAFF&resid=de165ddf5d02daff%219342&app=Excel

Imke

---

_{Monday, May 18, 2015 6:27 AM}

Many thanks!

However there is something that still isn't giving me the desired outcome of step chart... 

Say my dates & values are: 

3.1.14 - 10

10.10.14- 12

11.12.14- 11

3.3.15- 15

2.2.16- 10

6.6.16- 16

22.6.16- 13

I linked these to my own "DatesTbl" and when I chose line chart I see a line chart instead of a step one.

I think the issue is that you have a table with a value for each day, while I don't. 

Any workarounds?

עמית

---

_{Monday, May 18, 2015 4:16 PM}

sorry, these example data didn't make sense here. Deleted most of them to create gaps like yours - and it works.

But the key is that the table is not linked to the date table (have a look at my mail above:-))

If your existing date table for other reasons needs to be connected with your Table, just create another disconnected one that you refer to in that formula (and which you drag your date values from for the report)

Imke

---

_{Wednesday, July 20, 2016 5:54 PM}

Hi,

I have to create chart for same date different time how to do it??

Sanket

---

_{Saturday, March 2, 2019 4:16 PM}

Why the DimDate-table needs to be connected?
Also I have an ID pulled into the table, normally the row context would work as a filter automatically. In this case though, it does not seem to work. Wonder if this is due to this disconnected table? Thanks

---

_{Sunday, March 3, 2019 2:41 AM}

Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Step Chart with linear date axis.
With multiple IDs (Names)
Grouped dates into weeks for readability over 3 years.
Used PQ Fill() to allocate dates.
One Date Table.
http://www.mediafire.com/file/f757ooo5aervz93/12_13_18.xlsx/file
http://www.mediafire.com/file/4pbm4sb4ylckdh6/12_13_18.pdf/file