Forum Discussion

smercy's avatar
smercy
New Member
1 year ago
Solved

PowerBi previous row values

I have a fact table which has discontinous dates. 

EIDCompleted DateOutofServiceTest resut
A1-JanNoPass
B1-JanNopass
C4-JanNopass
A5-FebNopass
A7-MarNofail
A6-JunYesnull
C6-JunNoFail


If there is no data for a particular month, it should get previous entered value. Eg, here for  A there is no date for Apr, may. so it should take last entered value march and populate for apr and may

Expected outcome: i have given expected missed values  in bracket

EIDJanFebmarAprmayjune
APasspassfail(fail)(fail)OutOfService
BPass(pass)(pass)(pass)(pass)(pass)
CPass(pass)(pass)(pass)(pass)fail
Need Help

6 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

     

    Here’s the simplest and most reliable way I’ve found to “carry forward” previous values when dates are missing:

     

    1. Create a full calendar table covering all periods (months/years) you want.


    2. Link your calendar to your fact table on the date field (single direction, calendar ➔ fact).


    3. Use this DAX measure:LastNonBlankStatus =
    VAR currEID = SELECTEDVALUE(Fact[EID])
    VAR currDate = MAX(Calendar[Date])
    RETURN
    CALCULATE(
    LASTNONBLANK(Fact[Status], 1),
    FILTER(
    Fact,
    Fact[EID] = currEID &&
    Fact[Date] <= currDate
    )
    )

     

    Why this works: It dynamically fills any gaps in your report, so every month gets the last known value per EID even if that row didn’t exist in your fact table. Works perfectly with slicers and visuals.

     

    For static tables, Power Query’s Fill Down also works, but you lose interactivity. If your visuals break, double-check your table relationships 

    • smercy's avatar
      smercy
      New Member
      1 year ago

      I have tried this step already. It seems to work fine but when i add slicer, its giving incorrect reults.

      This outcome is the expected.


      when date slicer is added.


      Not sure if modelling is wrong . 



      For expanded table : 

      GroupDateTable =
      ADDCOLUMNS (
          CROSSJOIN (
              VALUES(fact_scale_verification[Eid]),
              values(DateTable[Date])
          ),
          "Index", RANKX (
              FILTER (
                  DateTable,
                  DateTable[Date] <= EARLIER(DateTable[Date])
              ),
              DateTable[Date],,DESC
          )
      )
      ------- 
      lastniu_measure = CALCULATE(max(GroupDateTable[NIU]),
       CALCULATETABLE(LASTNONBLANK('GroupDateTable'[Date],
       CALCULATE(MAX(GroupDateTable[NIU]))),
       DATESBETWEEN('GroupDateTable'[Date],
       MINX(ALL('GroupDateTable'),'GroupDateTable'[Date]),MAX('GroupDateTable'[Date]))))
      ---------

      TestValue =
      var lastniu = [lastniu_measure]
      RETURN
      if(ISBLANK(SELECTEDVALUE(GroupDateTable[Pass/fail])),
      if(SELECTEDVALUE(GroupDateTable[NIU])=1,"NIU",
      if(lastniu=1," ","Skip")),SELECTEDVALUE(GroupDateTable[Pass/fail]))



       

    • smercy's avatar
      smercy
      New Member
      1 year ago

      we dont have this column as per ur logic

      VAR CurrentDate = ExpandedTable[Completed Date]

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi   smercy

    Create a separate calendar table covering all periods so values can be assigned to them even if they're not present in the fact table and then create this measure. 

    Last Nonblank Value = 
IF (
    SELECTEDVALUE ( 'DataTable'[OutofService] ) = "Yes",
    "Out of Service",
    CALCULATE (
        LASTNONBLANKVALUE ( DateTable[Month], MAX ( 'DataTable'[Test resut] ) ),
        FILTER ( ALL ( DateTable ), DateTable[Date] <= MAX ( DateTable[Date] ) )
    )
)

    Please see attached sample pbix.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi smercy ,

     

    As a supplement, you can refer to the following formula, which I hope will help you.

     

    1.create data table:

    Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2024, 12, 31 ) ),
    "YearMonth", FORMAT ( [Date], "YYYY-MM" )
)

     

    2. then use below formula to create measure:

    Last Nonblank Value = 
IF (
    SELECTEDVALUE ( 'Fact'[OutofService] ) = "Yes",
    "Out of Service",
    LOOKUPVALUE (
        'Fact'[Test resut],
        'Fact'[EID], SELECTEDVALUE ( 'Fact'[EID] ),
        'Fact'[Completed Date], 
        CALCULATE (
            MAX ( 'Fact'[Completed Date] ),
            FILTER (
                ALL ( 'Fact' ),
                'Fact'[EID] = SELECTEDVALUE ( 'Fact'[EID] ) &&
                'Fact'[Completed Date] <= MAX ( 'Date'[Date] )
            )
        )
    )
)

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.