Forum Discussion

L1102's avatar
L1102
Icon for Helper I rankHelper I
9 months ago

Calculating Latest Weeks & Comparing to Same Timeframe PY

Hi All,   Looking for advice, I'm trying create a filter that has lastest week options for the end user on my dashboard ie. (YTD, L4,L12,L52). I have the following dax code to call the lastest time...
v-echaithra's avatar
v-echaithra
Icon for Community Support rankCommunity Support
8 months ago

Hi L1102 ,

Use a proper Calendar table

Relationship:

Calendar[Date] > Data_Dump_Sheet[Week End]
No relationship from Latest Time Periods > fact table, it must remain disconnected.

Base measure
[Sales] = SUM ( Data_Dump_Sheet[$ Sales] )

Current Period which is dynamic
Sales_Selected Period =
VAR SelectedDates =
CALCULATETABLE (
VALUES ( 'Calendar'[Date] ),
TREATAS (
VALUES ( 'Latest Time Periods'[Date] ),
'Calendar'[Date]
))
RETURN
CALCULATE ( [Sales], SelectedDates )

This applies L4 / L12 / L52 / YTD dynamically.

Prior Year with same timeframe

Sales_Selected Period PY =
VAR SelectedDates =
CALCULATETABLE (
VALUES ( 'Calendar'[Date] ),
TREATAS (
VALUES ( 'Latest Time Periods'[Date] ),
'Calendar'[Date]
))
VAR PYDates =
DATEADD ( SelectedDates, -1, YEAR )
RETURN
CALCULATE ( [Sales], PYDates )


Variance measures:

Sales_1 = [Sales_Selected Period] - [Sales_Selected Period PY]

Sales_%_1 = DIVIDE ( [Sales_1], [Sales_Selected Period PY] )

Hope this helps.
Thank you.

  • v-echaithra's avatar
    v-echaithra
    Icon for Community Support rankCommunity Support
    8 months ago

    Hi L1102 ,

    We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

    Best Regards,
    Chaithra E.

  • L1102's avatar
    L1102
    Icon for Helper I rankHelper I
    8 months ago

    Hi v-echaithra ,

     

    A couple of questions...

     

    1. A. Is this dax measure specific, and need to use this dax for all measures in the model?

    Reason I ask is because I have many measures and was hoping not to need to create all new ones. I also have multiple data tables rolling into the model.

    B. Would this work with as a calculated group using syntax "SelectedMeasure"?

     

    2. If I disconnect my relationship between Lastest Time Period & Fiscal Calendar, is there another way to get the L4, L12, L52, YTD visual below for the end user?

     

     

    Let me know your thoughts.

     

    Thank you.

    Laura

     

    • v-echaithra's avatar
      v-echaithra
      Icon for Community Support rankCommunity Support
      8 months ago

      Hi L1102 ,

      You do NOT need to recreate all of your business measures.
      Your existing measures like [Sales], [Units], [Margin], etc. should remain exactly as they are.
      The key is that the logic for L4 / L12 / L52 / YTD and Same Timeframe PY should not live inside each base measure. That logic belongs in a time-filtering layer that wraps those measures.

      Yes, the best and most scalable solution is to implement this using a Calculation Group with SELECTEDMEASURE().

      This gives you no measure duplication, works automatically across all existing and future measures and works with multiple fact tables.

      Keeping Latest Time Periods disconnected is correct and required. The slicer still works because the slicer filters the Latest Time Periods table, TREATAS() explicitly maps those selected dates onto Calendar[Date]. The Calendar table then filters the fact tables through normal relationships which allows you to use one slicer for L4 / L12 / L52 / YTD.

      Please provide the sample Pbix file. How to provide sample data in the Power BI Forum

      You can refer the following link to upload the file to the community.
      How to upload PBI in Community

      We are available to support you and are committed to helping you reach a resolution.

      Best Regards,
      Chaithra E.