Forum Discussion

krnfds's avatar
krnfds
Regular Visitor
2 years ago

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.

Need Help

5 Replies

    • krnfds's avatar
      krnfds
      Regular Visitor
      2 years ago

      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's avatar
        xifeng_L
        Super User
        2 years ago

        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~