create missing data using previous non blank date value

[Varan_15]

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	holiday check	customer ID	Sales
24/03/2024 0:00	weekday	1100016	350
25/03/2024 0:00	weekday	1100016	345
26/03/2024 0:00	weekday	1100016	345
27/03/2024 0:00	weekday
28/03/2024 0:00	weekday
29/03/2024 0:00	Weekend
30/03/2024 0:00	Weekend
31/03/2024 0:00	weekday	1100016	345
01/04/2024 0:00	weekday	1100016	345
02/04/2024 0:00	weekday	1100016	345
03/04/2024 0:00	weekday	1100016	345
04/04/2024 0:00	weekday	1100016	345
05/04/2024 0:00	Weekend
06/04/2024 0:00	Weekend

expected result:

highlighted color data is refering last date of non blank value and customer as well. Kindly suggest how to create measure/column

Thanks in advance 

[rajendraongole1]

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]

@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

[bhanu_gautam]

@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]

@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]

@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 · Accepted solution]

@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