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:

dateholiday checkcustomer IDSales
24/03/2024 0:00weekday1100016350
25/03/2024 0:00weekday1100016345
26/03/2024 0:00weekday1100016345
27/03/2024 0:00weekday  
28/03/2024 0:00weekday  
29/03/2024 0:00Weekend  
30/03/2024 0:00Weekend  
31/03/2024 0:00weekday1100016345
01/04/2024 0:00weekday1100016345
02/04/2024 0:00weekday1100016345
03/04/2024 0:00weekday1100016345
04/04/2024 0:00weekday1100016345
05/04/2024 0:00Weekend  
06/04/2024 0:00Weekend  

 

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 

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

6 Replies

  • 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

         

  • 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