Subtotals for a previous week

[Raul]

Hello,

I have two tables: Calendar table (with Data, Year, Qtr, Month and Week fields) and SalesTable (with de Sales field). I need the difference between sales of week previous year (column DIF)

	2017		2018
	Sales	DIF	Sales	DIF
T1	730.000,00		635.000,00	-95.000,00
Jan	91.350,00		136.800,00	45.450,00
1	300,00		27.500,00	27.200,00
2	5.800,00		13.500,00	7.700,00
3	31.000,00		37.100,00	6.100,00
4	28.100,00		54.500,00	26.400,00
5	10.200,00		4.200,00	- 6.000,00
6	15.950,00			15.950,00

I have create the measure DIF:

DIF = 

VAR ActualYear = SELECTEDVALUE(Calendar[Year])

VAR ActualWeek=SELECTEDVALUE(Calendar[Week])

RETURN

[Sales] - CALCULATE([Sales];FILTER(ALL(Calendar); Calendar[Week]=ActualWeek && Calendar[Year]=ActualYear-1))

where [Sales]=SUM(SalesTable[Sales]), and the result are this:

	2017		2018
	Sales	DIF	Sales	DIF
T1	730.000,00		635.000,00	635.000,00
Jan	91.350,00		136.800,00	136.800,00
1	300,00		27.500,00	27.200,00
2	5.800,00		13.500,00	7.700,00
3	31.000,00		37.100,00	6.100,00
4	28.100,00		54.500,00	26.400,00
5	10.200,00		4.200,00	- 6.000,00
6	15.950,00			15.950,00

It's correct on the week row but it isn't on the subtotals rows for Month and Quarter. What it's wrong?

Thank you very much for all community.

[v-yulgu-msft · Accepted solution]

Hi @Raul,

Please refer to the attached sample .pbix file.

Regards,

Yuliana Gu

[Raul]

Hello @v-yulgu-msft,

One more question about your post. If I want to filter for a year and return the difference with respect to the previous one, without showing the years in columns, how should it be done? Because now if you filter by the year field of the Calendar table, the DIF column returns the same value as the Year column.

Thanks a lot!

[Raul]

EXCELLENT @v-yulgu-msft!!!!

A complex but an effective solution. Thank you very much!!!!