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

rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

Hi Varan_15 - you can fill the blank values in power query editor with fill down transformation. 

 

 

Or you can try with below measure too

 

LastNonBlankSales1 =
CALCULATE(
    LASTNONBLANKVALUE(
        HCD[date],
        CALCULATE(MAX(HCD[Sales]))
    ),
    ALLEXCEPT(HCD, HCD[Customer ID])
)

 

Hope it works

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

 

  • Varan_15's avatar
    Varan_15
    Helper III
    2 years ago

    rajendraongole1 ,

     

    Thanks for the update.

     

    fill will not workout becasue in the original table there is dates are missing hence i'm using calendar table to full month completion.

    for the measure which you updated it's capturing same value for all the days.. if you see (march 24 will be 350 sales),

     

    Plase note : as per monthly date if any blank in middle dates it should take previous value that's requirement

     

    Thanks