Report is not showing values in three columns after incremental refresh.

[Atl]

Hello everyone,

Issue:
Report is not showing values in three columns after incremental refresh.

which three columns:
First Book Closure = CALCULATE(FIRSTDATE('SSCS Eligible Days'[Date]), ALL(dim_date[Date]),'SSCS Eligible Days'[Eligible Days]=1)

Latest Book Closure = CALCULATE(LASTDATE('SSCS Eligible Days'[Date]), ALL(dim_date[Date]),'SSCS Eligible Days'[Eligible Days]=1)

Days Since Last Book Closure = CALCULATE(CALCULATE(DATEDIFF(LASTDATE('SSCS Eligible Days'[Date]), TODAY(), DAY),'SSCS Eligible Days'[Eligible Days] = 1), ALL(dim_date[Date]))

More information about those three columns:
First book closure finds the earliest (first) date where a 'book closure' occurred.
Latest book closure finds the most recent (latest) date where a 'book closure' occurred.
Days Since Last Book Closure calculates the number of days from the most recent book closure date to today.
Latest book closure is 5/20/2024 (latest value) and 5/20/2021(oldest value).
First book closure is 8/7/2019 (oldest/first value) and is 11/1/2022 (new/last value).

Incremental refresh details:
RangeStart is 1/30/2022 12AM and RangeEnd is 1/31/2022 12AM. Type is Date/Time for both.
Incremental refresh bringing in six years worth of data and refreshing every month.
Incremental refresh on three fact tables. Fact_inventory , Fact_sales and Fact_purchases.

Applied steps details for those three fact tables:
let
Source = Sql.Database("ABCD.corp", "BackOffice"),
dbo_RWSales = Source{[Schema="dbo",Item="RWSales"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_RWSales,{{"SalesAmount", Currency.Type}, {"COGSAmount", Currency.Type}, {"SalesQuantity", Int64.Type}, {"DiscountAmount", Currency.Type}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Date_Key", "StoreNumber", "ShiftNumber", "UPCItemID", "Description", "DepartmentNumber", "SubdepartmentNumber", "SaleType", "PackSize", "SalesAmount", "SalesQuantity", "COGSAmount", "DiscountAmount", "Book_Date_Key", "File_Name", "loaddttm"}),
#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",3010,16,Replacer.ReplaceValue,{"DepartmentNumber"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",45,4,Replacer.ReplaceValue,{"DepartmentNumber"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "UPC_Key", each Number.ToText([StoreNumber])&"-"&[UPCItemID]),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each true),
#"Reordered Columns1" = Table.ReorderColumns(#"Filtered Rows1",{"UPC_Key", "StoreNumber", "UPCItemID", "Date_Key", "ShiftNumber", "Description", "DepartmentNumber", "SubdepartmentNumber", "SaleType", "PackSize", "SalesAmount", "SalesQuantity", "COGSAmount", "DiscountAmount", "Book_Date_Key", "File_Name", "loaddttm"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"UPC_Key", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each Date.FromText(Number.ToText([Date_Key]))),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type date}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Changed Type2",{"UPC_Key", "StoreNumber", "UPCItemID", "Date_Key", "Custom", "ShiftNumber", "Description", "DepartmentNumber", "SubdepartmentNumber", "SaleType", "PackSize", "SalesAmount", "SalesQuantity", "COGSAmount", "DiscountAmount", "Book_Date_Key", "File_Name", "loaddttm"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns2",{{"Custom", "Sale_Date"}, {"SalesAmount", "TotalSalesAmount"}, {"COGSAmount", "TotalCOGSAmount"}, {"DiscountAmount", "TotalDiscountAmount"}}),
#"Replaced Value2" = Table.ReplaceValue(#"Renamed Columns1",45,4,Replacer.ReplaceValue,{"DepartmentNumber"}),
#"Removed Other Columns" = Table.SelectColumns(#"Replaced Value2",{"UPC_Key", "StoreNumber", "UPCItemID", "Date_Key", "Sale_Date", "ShiftNumber", "Description", "DepartmentNumber", "SubdepartmentNumber", "SaleType", "PackSize", "TotalSalesAmount", "SalesQuantity", "TotalCOGSAmount", "TotalDiscountAmount"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Sale_Date", type datetime}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type3", each [Date_Key] >= Date.Year(RangeStart)*10000+Date.Month(RangeStart)*100+Date.Day(RangeStart) and [Date_Key] < Date.Year(RangeEnd)*10000+Date.Month(RangeEnd)*100+Date.Day(RangeEnd))
in
#"Filtered Rows"

let
Source = Sql.Database("ABCD.corp", "BackOffice"),
dbo_RWInventory = Source{[Schema="dbo",Item="RWInventory"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_RWInventory,{{"AverageCost", type number}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Date_Key", "StoreNumber", "ShiftNumber", "UPCItemID", "Description", "PackSize", "AccountNumber", "QuantityOnHand", "AverageCost", "CaseItemID"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Date_Key] >= Date.Year(RangeStart)*10000+Date.Month(RangeStart)*100+Date.Day(RangeStart) and [Date_Key] < Date.Year(RangeEnd)*10000+Date.Month(RangeEnd)*100+Date.Day(RangeEnd))
in
#"Filtered Rows"

let
Source = Sql.Database("ABCD.corp", "BackOffice"),
dbo_RWPurchases = Source{[Schema="dbo",Item="RWPurchases"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_RWPurchases,{{"PurchaseAmount", Currency.Type}, {"PurchaseQuantity", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"File_Name", "loaddttm"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Date_Key] >= Date.Year(RangeStart)*10000+Date.Month(RangeStart)*100+Date.Day(RangeStart) and [Date_Key] < Date.Year(RangeEnd)*10000+Date.Month(RangeEnd)*100+Date.Day(RangeEnd))
in
#"Filtered Rows"

Semantic model in power bi service was failing due to long refresh times.
So I copied that model and created a new model with incremental refresh to resolve that issue. Both models and reports are published in production workspace for verification.
Original report showing values in column first book closure, latest book closure, days since last book closure however new report with incremental refresh is not showing values in those three columns.

Please help. Thank you in advance. 

[Atl]

Bumping for visibility 

[Anonymous]

HI @Atl,

AFAIK, the incremental refresh requires the mechanism to filtered by date values.
Have you tried add a column to convert the datekey to date values and apply incremental refresh on the date field instead of extract and combine form data values to integer? (these calculated column expressions are based on date values instead of integer values)

Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng

[Atl]

Hi @Anonymous , thank you for your help. Are you saying add a new custom column name date = Date.FromText(Text.Start(Text.From([Date_Key]), 4) & "-" & Text.Middle(Text.From([Date_Key]), 4, 2) & "-" & Text.End(Text.From([Date_Key]), 2))

New column's data type is set to Date/Time. Do that for all three fact tables and use new column for incremental refresh. Apply rangestart and rangeend on new column.   Am i correct?

No need to change those three calculated columns (First Book Closure etc..) correct? 

Regards,