Forum Discussion

Amirolia's avatar
Amirolia
New Member
5 months ago
Solved

Dynamic WoW Variance against a Persistent Historical Snapshot in Power BI

Hi experts,

I have a challenge regarding Incremental Refresh and Data Persistence.

The Setup: I connect to a live ERP database. Since the database doesn't keep historical stock/accruals, I implemented Incremental Refresh with a SnapshotDate (using DateTime.LocalNow()) to archive data every week.

The Goal:

  1. When a new refresh happens (e.g., Week 13), I want Power BI to keep the Week 12 data exactly as it was (frozen).

  2. I need a DAX measure to calculate the difference: [Current Week Value] - [Value from the Previous Week's Snapshot].

The Problem: How can I ensure that when I subtract the values, Power BI correctly identifies the "Last Week's Snapshot" even though the live data for those dates might have changed in the source? Does LOOKUPVALUE work across different snapshot partitions, or should I use a specific CALCULATE with PREVIOUSMONTH/WEEK logic?

Export Requirement: Also, I need to export this matrix to Excel so that the formulas or the connection remains Dynamic/Refreshable, not just a static copy-paste.

Any guidance on the DAX logic or the best "Analyze in Excel" workflow would be appreciated!

Need Help
  • Natarajan_M's avatar
    Natarajan_M
    5 months ago

    Hi Amirolia ,

    Incremental refresh partitions are immutable once written. Week 12 data won't be touched when Week 13 refreshes : as long as RangeStart/RangeEnd correctly filter your SnapshotDate column.

    Check whether the below approach works for you :
    Instead of lookup and previous week I think you can use the calculate approach 

    _LatestSnap = MAXX(ALL(Fact[SnapshotDate]), Fact[SnapshotDate])
    _PrevSnap = MAXX(FILTER(ALL(Fact[SnapshotDate]), Fact[SnapshotDate] < [_LatestSnap]), Fact[SnapshotDate])

    WoW Delta V1 =
    CALCULATE(SUM(Fact[V1]), ALL(Fact[SnapshotDate]), Fact[SnapshotDate] = [_LatestSnap])
    - CALCULATE(SUM(Fact[V1]), ALL(Fact[SnapshotDate]), Fact[SnapshotDate] = [_PrevSnap])


    Use Analyze in Excel : it creates an ODC-connected PivotTable that stays live against your published semantic model. Users just hit Refresh in Excel.
    Avoid Export > CSV that's static.

    Thanks ,
    If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

    For more Power BI tips and discussions, let’s connect on LinkedIn:
    https://www.linkedin.com/in/natarajan-manivasagan

    Cheers!


4 Replies

  • Natarajan_M's avatar
    Natarajan_M
    Icon for Super User rankSuper User
    5 months ago

    Hi Amirolia ,

    Incremental refresh partitions are immutable once written. Week 12 data won't be touched when Week 13 refreshes : as long as RangeStart/RangeEnd correctly filter your SnapshotDate column.

    Check whether the below approach works for you :
    Instead of lookup and previous week I think you can use the calculate approach 

    _LatestSnap = MAXX(ALL(Fact[SnapshotDate]), Fact[SnapshotDate])
    _PrevSnap = MAXX(FILTER(ALL(Fact[SnapshotDate]), Fact[SnapshotDate] < [_LatestSnap]), Fact[SnapshotDate])

    WoW Delta V1 =
    CALCULATE(SUM(Fact[V1]), ALL(Fact[SnapshotDate]), Fact[SnapshotDate] = [_LatestSnap])
    - CALCULATE(SUM(Fact[V1]), ALL(Fact[SnapshotDate]), Fact[SnapshotDate] = [_PrevSnap])


    Use Analyze in Excel : it creates an ODC-connected PivotTable that stays live against your published semantic model. Users just hit Refresh in Excel.
    Avoid Export > CSV that's static.

    Thanks ,
    If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

    For more Power BI tips and discussions, let’s connect on LinkedIn:
    https://www.linkedin.com/in/natarajan-manivasagan

    Cheers!


  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 months ago

    There are no weekly partitions in Incremental Refresh.  Your only choices are Day/Month/Quarter/Year,  and these are regular calendar dates, not fiscal calendar dates.

  • v-achippa's avatar
    v-achippa
    Icon for Community Support rankCommunity Support
    5 months ago

    Hi Amirolia,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Thank you Natarajan_M and lbendlin for the prompt response.

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

     

    Thanks and regards,

    Anjan Kumar Chippa

    • v-achippa's avatar
      v-achippa
      Icon for Community Support rankCommunity Support
      5 months ago

      Hi @Amirolia,

       

      We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

       

      Thanks and regards,

      Anjan Kumar Chippa