Tracking Daily Revenue Delta Between Static and Dynamic Tables in Power BI

[sumiteshkumar]

i am trying to find delta (difference) between 2 tables (vw__sales_baseline) & (vw__sales_current) loaded in power bi.
both the tables are identical (pulled from same source), i have just duplicated the table and renamed the tables so that i can identify.
my requirement is to make (vw__sales_baseline) static for a week (MON-SAT) and dynamic (SUN) so that when compared to (vw__sales_current) on MON-SAT i can find delta of revenue everyday changes, and on SUN (vw__sales_baseline) should refresh and make a new snapshot for next week.
i need to compare the delta (everyday)

also i saw few article where incremental refresh has been used but again i have issue in the same as i dont have date column in the tables, i created a RefershFlag column in the same table, but it did not worked.
also, i cant use schedule refresh option as the other tables in the report refresh everyday.

here is the existing query i have for table that is loaded

let
Source = Databricks.Catalogs(HostName, HTTPPath, [Catalog=Catalog, Database="cubes", EnableAutomaticProxyDiscovery=null]),
vw__sales_Table = Source{[Item="vw__sales"]}[Data],
#"Filtered Rows" = Table.SelectRows(vw__sales_Table, each ([SalesOrgID] = "Dodo|SAP|400" or [SalesOrgID] = "Dodo|SAP|200")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "RefreshFlag", each let
Today = DateTime.LocalNow(),
RefreshFlag = if Date.DayOfWeek(Today, Day.Sunday) = 0 then 1 else 0
in
RefreshFlag),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each true)
in
#"Filtered Rows1"

i need help in making table (vw__sales_baseline) static (MON-SAT) also have snapshot to compare with table (vw__sales_current) when static and again it should refresh on SUN so that table (vw__sales_baseline) can have new snapshot for comparison

here is the sample test data i have added for text (pulled from excel)

https://drive.google.com/file/d/15pgKM-e78Zk7P6FVRkcYsmTg2ald8HPP/view?usp=drivesdk 

[rajendraongole1 · Accepted solution]

Hi @sumiteshkumar  -You need a way to persist data so that vw__sales_baseline remains unchanged Monday-Saturday and updates only on Sunday.

Steps:
Create a Storage Table:

Use an external database (Databricks, SQL Server, or SharePoint List) to store the vw__sales_baseline snapshot each Sunday.
Modify Your Power Query to Read from This Storage Table:

Load vw__sales_baseline only if today is not Sunday (i.e., Monday-Saturday).
Refresh vw__sales_baseline on Sunday and store the latest data.

If you have access to an external database (SQL, Databricks, SharePoint List).

Hope this helps.