Difficulty pulling data onto Line Chart

[krnfds]

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.

[xifeng_L]

Can you provide some sample data or pbix file?

[krnfds]

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. 

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!

[xifeng_L]

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

Day	Backlog 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~

[krnfds]

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

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.

[krnfds]

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.