I'v table for customer data table for 2024,2025if i filter in my calendar table = 25'Q4 then metrics table show like belowweek is dynamic will add every week.DAX Current Year = SUM(Customer table[value]) Rows = Country (coming from country table)Column = Week number (customer data table)Value1 = Current Year(DAX)Value 2 = Previous year (DAX) - Needed 40 41 42 43 44 45 46India 100 120 150 200 240 300 340Canana 50 60 80 70 90 120 100US 60 80 70 50 10 20 30 Result needed 40 41 42 43 44 45 46India 100 220 370 570 810 1100 1450Canana 50 110 190 260 350 470 570US 60 140 210 260 270 290 320

Hi Anonymous As you mentioned , you want to calculate the Cumulative prior year till 52 weeks instead of 48 weeks. I took sample data based on your inputs and replicated from my side. Please try to use these measures , you will get the desire output. Please refer below output snaps and attached .PBIX file.Thanks.

Cumulative Fiscal week DAX Calc | Microsoft Fabric Community

12 Replies

amitchandak
Super User
9 months ago
Anonymous , I am assuming you have two column in Date table FY Year and Fy Week , assuming both are numeric or create one
For Last Week have these two too

FY year Week = [FY Year] *100 + [FY Week]
Week Rank = RANKX('Date','Date'[FY year Week],,ASC,Dense)

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Cumm This year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),

'Date'[FY Year]=(max('Date'[FY Year]) ) && 'Date'[Fy Week]<=max('Date'[Fy Week]) ))

Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),

'Date'[FY Year]=(max('Date'[FY Year]) -1) && 'Date'[Fy Week]=max('Date'[Fy Week]) ))

Cumm

Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),

'Date'[FY Year]=(max('Date'[FY Year]) -1) && 'Date'[Fy Week]<=max('Date'[Fy Week]) ))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Zanqueta
Super User
9 months ago
Hi Anonymous
I repdroduce here and you just create a measure:

Current Year Cumulative = VAR CurrentWeek = MAX('Customer table'[FiscalWeek]) VAR CurrentYear = MAX('Customer table'[FiscalYear]) RETURN CALCULATE( SUM('Customer table'[Value]), FILTER( ALL('Customer table'[FiscalWeek]), 'Customer table'[FiscalWeek] <= CurrentWeek ) )

If this answer was helpful in any way, I would be pleased to receive a 👍, as well as the satisfaction of seeing a DAX measure work for the first time without needing yet another FILTER.
Please mark it as the accepted solution. This helps other community members find the quickest path and saves them from another endless loop 🌀.
- Anonymous
  Not applicable
  9 months ago
  Working fine. previous year not works
  my calendar filter = 25'Q4 then it will show last year cumulative numbers but showing as blank data but we have datas. DAX Code applied below
  
  Test 3 =
  //_Previous Year Cumulative
  VAR CurrentWeek =
  VALUE ( MAX ( 'Data'[Order_Fiscal week] ) )
  VAR _PreviousYear =
  VALUE ( MAX ( 'Data'[fiscal year] ) ) - 1
  RETURN
  CALCULATE (
  [Value],
  FILTER (
  ALL (
  'Data'[Order_Fiscal week],
  'Data'[fiscal year]
  ),
  VALUE ( 'Data'[fiscal year] ) = _PreviousYear
  && VALUE ( 'Data'[Order_Fiscal week] ) <= CurrentWeek
  )
  )
  - Zanqueta
    Super User
    9 months ago
    Hi Anonymous,
    
    The issue lies in the way the filter context is being removed and applied. Your measure for Previous Year Cumulative returns BLANK because:
    You are using ALL('Orders ESET Actuals'[Order_Fiscal week], 'Orders ESET Actuals'[fiscal year]), which removes all filters, but then applies conditions that may not align with the current context.
    If the calendar filter is set to 2025 Q4, the measure looks for weeks from the previous year (2024) but within the original table context, which may not be correctly related to the calendar table.
    
    Use the calendar table to control the context and remove only the necessary filters (rather than removing everything). Example:
    
    Previous Year Cumulative = VAR CurrentWeek = MAX('Calendar'[FiscalWeek]) VAR PreviousYear = MAX('Calendar'[FiscalYear]) - 1 RETURN CALCULATE( [Order_QTD $], FILTER( ALL('Calendar'), 'Calendar'[FiscalYear] = PreviousYear && 'Calendar'[FiscalWeek] <= CurrentWeek ) )
    
    Checklist to make this work
    Confirm that the 'Calendar' table is related to 'Orders ESET Actuals' via the date or fiscal key.
    Ensure [Order_QTD $] is a measure that correctly sums values.
    If your filter is by quarter (25'Q4), the logic remains valid because we use MAX(FiscalWeek) to determine the cumulative range.
    
    Alternative for performance
    If you have large datasets, you can use DATESYTD or TOTALYTD with SAMEPERIODLASTYEAR:
    
    Previous Year Cumulative = CALCULATE( [Order_QTD $], DATESYTD(SAMEPERIODLASTYEAR('Calendar'[Date]), "30/06") )
    
    (Assuming the fiscal year ends on 30 June.)
    
    If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
    Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
- Anonymous
  Not applicable
  9 months ago
  amitchandak Zanqueta
  
  I can able to create previous year cumulative
  cumulative prior yearweek =
  
  VAR prioryearWeek = MAX ( 'Table'[Fiscal Week New] )
  RETURN
  CALCULATE (
  [value],
  SAMEPERIODLASTYEAR ( DATESQTD ( 'Calendar'[Date] ) ),
  FILTER (
  ALLSELECTED ( 'Table'[Fiscal Week New] ),
  'Table'[Fiscal Week New] <= prioryearWeek
  )
  )
  
  Issue:
  Currently, the cumulative DAX calculation for prior year only gives results up to Week 48, even though 52 weeks have already been completed.
  Request:
  We need support for an additional DAX measure that calculates:
  Cumulative Prior Year = (MAX Week Value for Prior Year) / (Weekly Value)
  Example: 570 / 50
  
  Suggest DAX Code : cumulative prior year week(correct code), cumulative prior year(divide)
  - Anonymous
    Not applicable
    8 months ago
    Hi Anonymous
    
    I tried to reproduce your scenario to find any possible solution but couldn't because of lack of exact data.
    
    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    
    Please show the expected outcome based on the sample data you provided.