Forum Discussion

Varan_15's avatar
Varan_15
Helper III
2 years ago
Solved

create missing data using previous non blank date value

Hi All,   I have customer wise sales table which is contain few blank dates and empty values , I have to consider last non blank value on same customer level kindly help.   actual data: date ...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    Varan_15 ,

     

    Create two columns using

     

    Filled Customer ID =
    VAR CurrentDate = 'Table'[date]
    RETURN
    IF(
    ISBLANK('Table'[customer ID]),
    CALCULATE(
    LASTNONBLANK('Table'[customer ID], 1),
    FILTER(
    'Table',
    'Table'[date] < CurrentDate
    )
    ),
    'Table'[customer ID]
    )

     

    And

    Filled Sales =
    VAR CurrentDate = 'Table'[date]
    VAR CurrentCustomer = 'Table'[Filled Customer ID]
    RETURN
    IF(
    ISBLANK('Table'[Sales]),
    CALCULATE(
    LASTNONBLANK('Table'[Sales], 1),
    FILTER(
    'Table',
    'Table'[Filled Customer ID] = CurrentCustomer &&
    'Table'[date] < CurrentDate
    )
    ),
    'Table'[Sales]
    )

     

    Attaching PBIX with your sample date

bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

Varan_15 , You can achieve it in Power Query also

 

Just click on Fill and select down it will get it done make sure correct column is selected

 

bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

Varan_15 ,

 

Create two columns using

 

Filled Customer ID =
VAR CurrentDate = 'Table'[date]
RETURN
IF(
ISBLANK('Table'[customer ID]),
CALCULATE(
LASTNONBLANK('Table'[customer ID], 1),
FILTER(
'Table',
'Table'[date] < CurrentDate
)
),
'Table'[customer ID]
)

 

And

Filled Sales =
VAR CurrentDate = 'Table'[date]
VAR CurrentCustomer = 'Table'[Filled Customer ID]
RETURN
IF(
ISBLANK('Table'[Sales]),
CALCULATE(
LASTNONBLANK('Table'[Sales], 1),
FILTER(
'Table',
'Table'[Filled Customer ID] = CurrentCustomer &&
'Table'[date] < CurrentDate
)
),
'Table'[Sales]
)

 

Attaching PBIX with your sample date