Unable to calculated difference using DAX

[Sekhar1]

Hi, 

I am getting error while trying to calculate difference between weeks. Please see below sample table 

Year	Year and Week No	Quarter	QuarterInYear	Totals	Date
2022	2022_WK07	Q1	Q1 2022	333	31-Mar-22
2023	2023_WK07	Q1	Q1 2023	1,344	31-Mar-23
2022	2022_WK07	Q1	Q1 2022	5,151	31-Mar-22
2023	2023_WK07	Q1	Q1 2023	600	31-Mar-23
2022	2022_WK07	Q1	Q1 2022	500	31-Mar-22
2023	2023_WK07	Q1	Q1 2023	874	31-Mar-23
2022	2022_WK07	Q1	Q1 2022	16,161	31-Mar-22
2023	2023_WK07	Q1	Q1 2023	15,151	31-Mar-23
2022	2022_WK07	Q1	Q1 2022	211,646	31-Mar-22
2022	2022_WK07	Q1	Q1 2022	51,561	31-Mar-22
2022	2022_WK07	Q1	Q1 2022	51	31-Mar-22
2022	2022_WK07	Q1	Q1 2022	5,151	31-Mar-22
2022	2022_WK07	Q1	Q1 2022	51,858	31-Mar-22
2022	2022_WK07	Q1	Q1 2022	7,894	31-Mar-22
2022	2022_WK07	Q1	Q1 2022	1,463	31-Mar-22
2023	2023_WK07	Q1	Q1 2023	1,582	31-Mar-23
2023	2023_WK07	Q1	Q1 2023	2,453	31-Mar-23
2023	2023_WK07	Q1	Q1 2023	3,586	31-Mar-23
2023	2023_WK07	Q1	Q1 2023	6,589	31-Mar-23
2023	2023_WK07	Q1	Q1 2023	59,674	31-Mar-23
2023	2023_WK07	Q1	Q1 2023	36,541	31-Mar-23

Year, Year and Week No  filters table below it.

Year 2, Year and Week No 2 filters table below it. 

I wanted to calculate difference based on user selection of filters and not getting any difference. 

I have used following DAX:- 

Difference =

VAR YearTotal =

    CALCULATE (

        SUM ( 'Table'[Totals] ),

            REMOVEFILTERS('Calendar Table 2'[Year]),

           REMOVEFILTERS ('Table'[Year and Week No 2]))

VAR PYearTotal =

    CALCULATE (

        SUM ( 'Table'[Totals] ),

            REMOVEFILTERS('Calendar Table'[Year]),

           REMOVEFILTERS ('Table'[Year and Week No]))

VAR Result = YearTotal - PYearTotal

Return

Result 

Can you please have a look and let me know where the error is. 

Thanks in advance for help. 

[Anonymous · Accepted solution]

Hi @Sekhar1 ,

According to your statement, I think the relationship between the column will cause this issue.I suggest you to create two unrelated slicer tables with [Year] and [Year and Week No] columns you need.

Data model:

Measures:

Sum of Total 1 = 
 CALCULATE(SUM('Table'[Totals]),USERELATIONSHIP('DimDate 1'[Date],'Table'[Date]))

Sum of Total 2 = 
 CALCULATE(SUM('Table'[Totals]),USERELATIONSHIP('DimDate 2'[Date],'Table'[Date]))

Difference = 
[Sum of Total 1] - [Sum of Total 2]

 Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Sekhar1 , iof you want to use two date slicers, refer these ways

Power BI Abstract Thesis: How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg

For Week on Week

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510