topic Re: Calculate previous years data without date, data aggregeted to weeks in DAX Commands and Tips

Calculate previous years data without date, data aggregeted to weeks

Viktor_ — Fri, 21 Mar 2025 13:04:07 GMT

Hi All,

I would like to ask some help.

I have an aggregated table to weeks with ordered quantity. I would like to calculate the previous years orders
on the same week by the Partner-Product_ID-WEEK level.
I have tried a lot of calculation, but neither works. Tried to get to clear the filter on weeks and after would like to get the WEEK_LY column value in the related row and then filter back the weeks.
The main aim is a measure but also calculated column is fine.
Also have to work correctly on totals in a matrix on all level.

Hope clearly define the problem and there is a solution.

Thank you in advance!

Partner	product_ID	Period	WEEK	WEEK_LY	Quantity_ordered	Quantity_ordered_LY
Partner 1	product 1	23P11	23W48	22W48	54	1
Partner 2	product 1	22P10	22W41	21W41	18	1
Partner 2	product 1	22P12	22W51	21W51	144	1
Partner 3	product 1	24P07	24W31	23W31	144	1
Partner 3	product 1	24P09	24W38	23W38	108	1
Partner 3	product 1	24P12	24W50	23W50	126	1
Partner 4	product 2	22P09	22W37	21W37	180	1
Partner 4	product 2	22P09	22W38	21W38	36	1
Partner 4	product 2	22P10	22W44	21W44	54	1
Partner 4	product 2	22P11	22W47	21W47	36	1
Partner 4	product 2	23P09	23W37	22W37	18	1
Partner 4	product 2	23P12	23W49	22W49	18	1
Partner 4	product 3	23P12	23W51	22W51	18	1
Partner 5	product 1	24P02	24W08	23W08	36	1
Partner 5	product 1	24P02	24W09	23W09	72	1
Partner 5	product 1	24P03	24W13	23W13	54	1

Re: Calculate previous years data without date, data aggregeted to weeks

bhanu_gautam — Fri, 21 Mar 2025 13:27:15 GMT

Viktor_ To ensure that the totals are calculated correctly in a matrix, you might need to use a slightly different approach. Here is an alternative measure that handles totals correctly:

DAX
Quantity_ordered_LY =
SUMX(
'YourTable',
CALCULATE(
SUM('YourTable'[Quantity_ordered]),
FILTER(
'YourTable',
'YourTable'[Partner] = EARLIER('YourTable'[Partner]) &&
'YourTable'[product_ID] = EARLIER('YourTable'[product_ID]) &&
'YourTable'[WEEK] = EARLIER('YourTable'[WEEK_LY])
)
)
)

Add the Quantity_ordered_LY measure to your matrix visualization in Power BI. This will show the previous year's orders for the same week at the Partner-Product_ID-WEEK level.

Re: Calculate previous years data without date, data aggregeted to weeks

Viktor_ — Sat, 22 Mar 2025 09:10:32 GMT

bhanu_gautam Thank you for your quick answer!
I tried and I have only grand total, there is no values for others. And total is not correct.
What could be the error?
Thank you!

Re: Calculate previous years data without date, data aggregeted to weeks

V-yubandi-msft — Wed, 07 May 2025 09:16:08 GMT

Hi Viktor_ ,

Thanks for sharing your result it helps pinpoint the issue.

Your grand total shows, but row level values are missing because EARLIER() doesn't work well in measures like SUMX(), as it lacks row context.

Use VAR and CALCULATE in your measure to properly maintain row context. This ensures correct calculations at both individual row and total levels in matrix visuals.

I hope this helps. Did I answer your question? Mark my post as a solution.

Re: Calculate previous years data without date, data aggregeted to weeks

V-yubandi-msft — Mon, 12 May 2025 04:38:31 GMT

Hi Viktor_ ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank You.

Re: Calculate previous years data without date, data aggregeted to weeks

V-yubandi-msft — Thu, 15 May 2025 09:07:09 GMT

Hi Viktor_ ,

Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.

Thank You.

Re: Calculate previous years data without date, data aggregeted to weeks

V-yubandi-msft — Mon, 19 May 2025 05:20:00 GMT

Hi Viktor_ ,

Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.