Search code examples
matrixpowerbidax

Take previous Cumulated Value if blank matrix visual

I have a matrix where Day on X-Axis, Year and Quarter on Y-axis. Cumulated amount on Values.

Matrix: enter image description here

wherever there is a blank, it should take the previous amount instead of displaying blank. for example, on Day 2 for 2024 Q1, it should show 787,544. On day 6,7,8 for 2023 Q4, it should show 10,201,258. below is the dax for cumulative amount.

Cumulative Amount = 
  var maxnum = MAX('Sheet1'[Day])
  var tmp = FILTER(ALL(Sheet1[Day]),'Sheet1'[Day]<=maxnum)
  return CALCULATE(SUM('Sheet1'[Amount]),tmp)
Solution

  • I'm assuming in your Matrix, you are using [Day] column from a date table. If so, update the measure to:

    Cumulative Amount = 
  var maxnum = MAX('YourDateTable'[Day])
  var tmp = FILTER(ALL(Sheet1[Day]),'Sheet1'[Day]<=maxnum)
  return CALCULATE(SUM('Sheet1'[Amount]),tmp)

    If it is all from one table - then you will need to have separate table for the Days. Create a new Calculated Table like:

    Matrix Days = DISTINCT(Sheet1[Day])

    or

    Matrix Days = SELECTCOLUMNS(GENERATESERIES(1, 9, 1), "Day", [Value])

    Then update your measure to:

    Cumulative Amount = 
  var maxnum = MAX('Matrix Days'[Day]) // <-- updated
  var tmp = FILTER(ALL(Sheet1[Day]),'Sheet1'[Day]<=maxnum)
  return CALCULATE(SUM('Sheet1'[Amount]),tmp)