Forum Discussion
Cumulative Fiscal week DAX Calc
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.
Super UserHi 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 🌀.
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)
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.
Hi Anonymous
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. so we can work on this.
Hi Anonymous
The most common reason dynamic weeks do not go up to Week 52 is that the Calendar table ends earlier than the fact data. Your DAX measures determine the “current week” based on the Calendar table, not the fact table, so if the Calendar only contains dates up to Week 48, the maximum fiscal week returned will be 48 even if the fact table already has data through Week 52. This effectively caps all time-based calculations at Week 48. You can confirm this by comparing the maximum date in the Calendar table with the maximum date in the fact table if the fact table’s date is later, it means the Calendar table is incomplete and needs to be extended to cover the full date range.
If everything appears correct but the issue persists, please provide the data.
Thanks.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.Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.