Forum Discussion

Sekhar1's avatar
Sekhar1
Frequent Visitor
3 years ago
Solved

Unable to calculated difference using DAX

Hi, 

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

 

YearYear and Week NoQuarterQuarterInYearTotalsDate
20222022_WK07Q1Q1 202233331-Mar-22
20232023_WK07Q1Q1 20231,34431-Mar-23
20222022_WK07Q1Q1 20225,15131-Mar-22
20232023_WK07Q1Q1 202360031-Mar-23
20222022_WK07Q1Q1 202250031-Mar-22
20232023_WK07Q1Q1 202387431-Mar-23
20222022_WK07Q1Q1 202216,16131-Mar-22
20232023_WK07Q1Q1 202315,15131-Mar-23
20222022_WK07Q1Q1 2022211,64631-Mar-22
20222022_WK07Q1Q1 202251,56131-Mar-22
20222022_WK07Q1Q1 20225131-Mar-22
20222022_WK07Q1Q1 20225,15131-Mar-22
20222022_WK07Q1Q1 202251,85831-Mar-22
20222022_WK07Q1Q1 20227,89431-Mar-22
20222022_WK07Q1Q1 20221,46331-Mar-22
20232023_WK07Q1Q1 20231,58231-Mar-23
20232023_WK07Q1Q1 20232,45331-Mar-23
20232023_WK07Q1Q1 20233,58631-Mar-23
20232023_WK07Q1Q1 20236,58931-Mar-23
20232023_WK07Q1Q1 202359,67431-Mar-23
20232023_WK07Q1Q1 202336,54131-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. 
 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

2 Replies