Need to fill blanks with previous data and show correct column Total as well

[VannurVali]

Hello everyone,

I am looking for suggestions on how to fill blank values with the last available date's value using a measure in Power BI. Below is a sample of my data:

Date Month Store_ID Product_Code Sales

01-01-2024	1	A	101	100
01-02-2024	2	A	101
01-03-2024	3	A	101	400
01-01-2024	1	B	102	300
01-02-2024	2	B	102
01-03-2024	3	B	102	500

Expected behavior:

• For February 2024, Store A, Product 101, if the Sales value is blank, it should take the January 2024 Sales value (100).

• For March 2024, Store B, Product 102, if the Sales value is blank, it should take the February 2024 Sales value (500).
  Note:
  The above table is just a reference. In reality, we don't have rows for months with blank sales in the fact table. Instead, we rely on a separate Dim_Calendar table for all dates.
  The fact table looks like this:

  Date Month Store_ID Product_Code Sales

  01-01-2024	1	A	101	100
  01-03-2024	3	A	101	400
  01-01-2024	1	B	102	300
  01-03-2024	3	B	102	500

  
  Here is the DAX measure I am currently using, which successfully achieves the forward-fill behavior for individual rows:
  _test display share count =
  VAR a = CALCULATE(
  [_test Base display share count],
  CALCULATETABLE(
  LASTNONBLANK('Dim_Calendar'[Date], CALCULATE([_test Base display share count])),
  DATESBETWEEN(
  'Dim_Calendar'[Date],
  MINX(ALL('Dim_Calendar'), 'Dim_Calendar'[Date]),
  MAX('Dim_Calendar'[Date])
  )
  )
  )
  RETURN
  IF([_test Base display share count] = BLANK(), a, [_test Base display share count])
  

  
  This measure works well for forward-filling the blank values, but I am facing an issue with the column totals — they do not correctly sum the forward-filled values.

  Could anyone please help me to modify this measure so that the column totals reflect the forward-filled data correctly?

  

  
  Thanks in advance!
  
  

  Best regards,
  Vannur Vali

[DVannurVali]

Hi @v-kpoloju-msft, @antfr99 , @Ashish_Excel, @pankajnamekar25,

I hope you're doing well!

Thank you for your prompt response and continued support.

Best Regards,
D. Vannur Vali

[v-kpoloju-msft · Accepted solution]

Hi @DVannurVali, 

Apologize for the delayed response. After thoroughly reviewing the details you provided, I reproduced the scenario again, and it worked on my end. I used it as sample data and successfully implemented it.

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @DVannurVali,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Ashish_Excel]

Hi,

This works fine

S = SUM(Data[Sales])

Last sale = CALCULATE([S],CALCULATETABLE(LASTNONBLANK('Calendar'[Date],CALCULATE([S])),DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),MAX('Calendar'[Date]))))

[v-kpoloju-msft]

Hi @VannurVali,

Thank you for reaching out to the Microsoft fabric community forum. Thank you @antfr99, @pankajnamekar25, for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.    

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @VannurVali,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @VannurVali,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[VannurVali]

Hi @v-kpoloju-msft, not working as expected.

[v-kpoloju-msft]

Hi @VannurVali,

After thoroughly reviewing the details you provided, I again reproduced the scenario again, and it worked on my end. I used it as sample data and successfully implemented it.

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @VannurVali,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @VannurVali,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-kpoloju-msft]

Hi @VannurVali,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[DVannurVali]

Hi @v-kpoloju-msft, It's not working as expected.

[v-kpoloju-msft]

Hi @VannurVali,
Thank you for reaching out to the Microsoft fabric community forum.

After thoroughly reviewing the details you provided, I reproduced the scenario again, and it worked on my end. I used it as sample data and successfully implemented it.

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[antfr99]

Hi @VannurVali 

I suggest the following measure which works with a Fact table and Dim_Calendar table ( relationship active on date ) :

Sales fill Total = 
VAR fillTable =
    ADDCOLUMNS(
        SUMMARIZE(
            FactTable,
            FactTable[Store_ID],
            FactTable[Product_Code],
            'Dim_Calendar'[Date]
        ),
        "fillValue",
        VAR CurrentDate = [Date]
        VAR CurrentStore = [Store_ID]
        VAR CurrentProduct = [Product_Code]
        RETURN
            CALCULATE(
                MAX(FactTable[Sales]),
                FILTER(
                    ALL('Dim_Calendar'),
                    'Dim_Calendar'[Date] <= CurrentDate
                ),
                FactTable[Store_ID] = CurrentStore,
                FactTable[Product_Code] = CurrentProduct
            )
    )

RETURN
    SUMX(fillTable, [fillValue])

The results with the column total showing are as per below :

In the matrix view you would have this which I think is similar to your scenario recap :

Above created with the assistance of AI.
Hope this helps
Antonio

[VannurVali]

Hi @antfr99, not working as expected.

[antfr99]

Hi @VannurVali 

Ok, well noted.  Just to explain the purpose of the code :

VAR fillTable =

Above creates a temporary table that stores all combinations of store, product, and date, along with the "filled" sales value for each.

SUMMARIZE(
    FactTable,
    FactTable[Store_ID],
    FactTable[Product_Code],
    'Dim_Calendar'[Date]
)

Above builds a table of all unique combinations of Store, Product and Date. This defines the granularity at which we want to calculate the filled sales. Every row represents one specific store, product, and date.

My assumption is that you would want time series per store/product, not across all stores/products.

If you only summarized by date (for example) and not as per above group then possibly it would return incorrect results ( possibly pulling the last known sales from a completely unrelated store or product.)

"fillValue",
VAR CurrentDate = [Date]
VAR CurrentStore = [Store_ID]
VAR CurrentProduct = [Product_Code]
RETURN

Above is saying: "At this date, for this store/product, what is the most recent sales value?" and works closely with below .

CALCULATE(
    MAX(FactTable[Sales]),
    FILTER(
        ALL('Dim_Calendar'),
        'Dim_Calendar'[Date] <= CurrentDate
    ),
    FactTable[Store_ID] = CurrentStore,
    FactTable[Product_Code] = CurrentProduct
)

Above :

-Changes the context to find the most recent sales value up to the current date.

-Ignores any filter on the calendar table (ALL('Dim_Calendar')) to get all earlier dates.

-Filters FactTable for the current store and product.

-Returns the maximum sales value up to that point — effectively the last known value

RETURN
    SUMX(fillTable, [fillValue])

Above :

-Iterates over all rows in the fillTable.

-Adds up all the filled sales values.

Hopefully the above explanation clarifies the logic. Can you advise why the code is not working ?  Is the logic incorrect or is there an error message in the code ?

Below is a link of the folder with the files , feel free to review.

Folder with sample files 

Antonio 

[pankajnamekar25]

Hello @VannurVali 

try this DAX code

test display share count (with total fix) =

VAR SelectedDate = MAX('Dim_Calendar'[Date])

VAR LastNonBlankSales =

    CALCULATE(

        [_test Base display share count],

        CALCULATETABLE(

            LASTNONBLANK('Dim_Calendar'[Date], CALCULATE([_test Base display share count])),

            DATESBETWEEN(

                'Dim_Calendar'[Date],

                MINX(ALL('Dim_Calendar'), 'Dim_Calendar'[Date]),

                SelectedDate

            )

        )

    )

RETURN

IF(

    ISINSCOPE('Dim_Calendar'[Date]),

    IF(

        ISBLANK([_test Base display share count]),

        LastNonBlankSales,

        [_test Base display share count]

    ),

    CALCULATE(

        [_test display share count],

        REMOVEFILTERS('Dim_Calendar')

    )

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[VannurVali]

Hi @pankajnamekar25 , not working as expected.