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.
