Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
krnfds
Regular Visitor

Difficulty pulling data onto Line Chart

Hi everyone,

I have a Power BI dashboard that pulls data from a csv file. I refreshed the data this past Monday. I have a card showing the total backlog#. Right now, the line chart is not pulling correctly where the quantity each week amounts to the total backlog#.

Instead, I want the chart to show what the total backlog# was for each Monday if that makes sense. I have a date table showing every date which is a Monday. The backlog quantity is pulled from a different table, capturing the count of rows with values in them in the Backlog Qty. column.

If I were to make this dashboard refresh everyday on the service, what would I need to do to make the line chart take the total backlog# only on Mondays, and save it so that it can be referenced for previous Mondays?

Please let me know if you have any questions if it does not make sense! Thank you so much!

 

This is what the graph looks like right now which is wrong. Each Monday should actually be showing a number ranging from 600-900 each week where it does not sum to the current backlog number of 882.

Screenshot 2024-06-14 152238.png

5 REPLIES 5
xifeng_L
Super User
Super User

Can you provide some sample data or pbix file?

Unfortunately, I cannot provide the whole file 😞

The backlog qty column looks like this where it shows a specific order. The cell is blank if it is not backlogged. 

krnfds_0-1718656877914.png

The x axis of the line chart uses this dax expression:

Backlog Qty. = COUNT ( 'PPIOO000_P34'[Backlog Qty] ) + 0
The y axis uses this:
Backlog Calendar =
VAR StartDate = DATE(2021, 1, 1)
VAR EndDate = DATE(2024, 12, 31)
VAR DateRange = ADDCOLUMNS(
    CALENDAR(StartDate, EndDate),
    "WeekDay", WEEKDAY([Date], 2)
)
RETURN
    FILTER(
        DateRange,
        [WeekDay] = 1
    )
 
Please let me know if you need additional info or have other questions!

I think you should change the table structure to the following, keeping a snapshot on each Monday.

 

DayBacklog Qty
4/1/2024

xxxxx

xxxxx

xxxxx

4/2/2024

xxxxx

xxxxx

xxxxx

4/3/2024

xxxxx

xxxxx

xxxxx

...

...

 

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

I added these columns to the main table. Would these help in any way?

krnfds_0-1718741363400.png

Total Backlog Qty =
COUNT(PPIOO000_P34[Backlog Qty])
 
Refresh Date and Weekday Name is using Power Query, so it would pull new info automatically when the data refreshed.

Sorry, I don't fully understand. How would I keep a snapshot automatically? 

Are you saying I should change my date table to show every day?

This is what my date table looks like where it spans all the way to the end of 2024. 

krnfds_0-1718726816408.png

 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.