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 , Create a new calculated columns using

 

LastNonBlankSales =
VAR CurrentDate = SalesData[date]
VAR CurrentCustomer = SalesData[customer ID]
RETURN
CALCULATE(
LASTNONBLANK(SalesData[Sales], SalesData[date]),
FILTER(
SalesData,
SalesData[customer ID] = CurrentCustomer &&
SalesData[date] <= CurrentDate &&
NOT(ISBLANK(SalesData[Sales]))
)
)

Varan_15's avatar
Varan_15
Helper III
2 years ago

bhanu_gautam ,

 

Thanks for your update, I couldn't able to enter lastnonblank second field "date" as it's considering only one field 

 

LastNonBlankSales =
VAR CurrentDate = SalesData[date]
VAR CurrentCustomer = SalesData[customer ID]
RETURN
CALCULATE(
LASTNONBLANK(SalesData[Sales], SalesData[date]),
FILTER(
SalesData,
SalesData[customer ID] = CurrentCustomer &&
SalesData[date] <= CurrentDate &&
NOT(ISBLANK(SalesData[Sales]))
)
)

 

(sales[date]) can't able to add. please suggest

  • 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