Variance between current and last year based on a year slicer from Calendar Table

[kalkhudary]

Hello All, 

I have a data model that includes 4 fact tables connected to a calender table through a YearMonth Index preserved on all the 4 fact tables and in the calender table. These tables are connected to the calendar table through a many-to-many relationship (see below the concept) with a single cross-filter direction from Calander to fact table. No relationship is required between the 4 fact tables meaning they don't need to read from each other data anything. It is only that each one connects to the Calander Table to utilize a global year slicer.

My issue comes here. I want to create a dax based on the global Year slicer. When I select a year in the Global Year slicer (2024) from calander Table, I want to see the variance % of calls received based on the last year (2023).

I have daxed this but there is something wrong, it is giving me 100% which is not the case, it should show 2.5% based on the values I have. Any step - by step help would be great, stuck on it for a day now.

Calls Received Variance % =

VAR Up_Arrow = UNICHAR(9650)

Var Down_Arrow = UNICHAR(9660)

VAR SelectedCallsReceivedLY = CALCULATE(SUM('Table1'[Value]), 'Tabl1'[Year]=(SELECTEDVALUE('Calendar Table'[Year-Global Filter]))-1, 'Table1'[Indicator]= "Calls received")

VAR CallsReceivedCurrentYear =

CALCULATE(SUM('Table1'[Value]),'Table1'[Indicator]="Calls received")

VAR Difference = CallsReceivedCurrentYear - SelectedCallsReceivedLY

VAR CallsReceivedPercentage = FORMAT(DIVIDE(Difference, CallsReceivedCurrentYear,0),"0.0%")

RETURN if(Difference<0,"-"&CallsReceivedPercentage&" " &Down_Arrow& "  from LY", "+"&CallsReceivedPercentage&" "&Up_Arrow& "  from LY")

[kalkhudary]

@v-nuoc-msft Worked perfectly. thanks so much. I also figured out that if I want to look at the last year current month, I can add the Filter(All(Table1 (date)) 

[v-nuoc-msft]

Hi @kalkhudary 

To calculate the variance percentage of calls received based on the last year, you need to ensure that your DAX formula correctly captures the values for the current year and the previous year.

The issue in your formula seems to be with the calculation of , where you are filtering the year directly in the  function. This might not be working as expected because the context transition might not be properly applied.

Try this:

Calls Received Variance % =
VAR Up_Arrow = UNICHAR(9650)
VAR Down_Arrow = UNICHAR(9660)
VAR SelectedYear = SELECTEDVALUE('Calendar Table'[Year-Global Filter])
VAR CallsReceivedCurrentYear =
    CALCULATE(
        SUM('Table1'[Value]),
        'Table1'[Indicator] = "Calls received",
        'Calendar Table'[Year-Global Filter] = SelectedYear
    )
VAR SelectedCallsReceivedLY =
    CALCULATE(
        SUM('Table1'[Value]),
        'Table1'[Indicator] = "Calls received",
        SAMEPERIODLASTYEAR('Calendar Table'[Date])
    )
VAR Difference = CallsReceivedCurrentYear - SelectedCallsReceivedLY
VAR CallsReceivedPercentage = DIVIDE(Difference, SelectedCallsReceivedLY, 0)

RETURN 
    IF(
        Difference < 0,
        "-" & FORMAT(CallsReceivedPercentage, "0.0%") & " " & Down_Arrow & " from LY",
        "+" & FORMAT(CallsReceivedPercentage, "0.0%") & " " & Up_Arrow & " from LY"
    )

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

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

[kalkhudary]

@v-nuoc-msft Thanks for the above. The idea of using sameperiodlastyear is good too. I tried but it is stilll not working. I want also to account for the fact if I want to look a previous Month previous year when selected the year from the global filter. 

Here is sample of dataset for one table that is connected to the Calender Table through the Index column: Table 1 connected to Calender table through the index column

Table1:

Indicator	Value	Year	Month	Index	YearMonthDay	Date
Number of calls received	2590	2023	1	202301	01/01/23	January 2023
Number of calls received	2455	2023	2	202302	02/01/23	February 2023
Number of calls received	2877	2023	3	202303	03/01/23	March 2023
Number of calls received	2801	2023	4	202304	04/01/23	April 2023
Number of calls received	4003	2023	5	202305	05/01/23	May 2023
Number of calls received	4143	2023	6	202306	06/01/23	June 2023
Number of calls received	4348	2023	7	202307	07/01/23	July 2023
Number of calls received	3850	2023	8	202308	08/01/23	August 2023
Number of calls received	3677	2023	9	202309	09/01/23	September 2023
Number of calls received	3314	2023	10	202310	10/01/23	October 2023
Number of calls received	2679	2023	11	202311	11/01/23	November 2023
Number of calls received	2649	2023	12	202312	12/01/23	December 2023
Number of calls received	3260	2024	1	202401	01/01/24	January 2024
Number of calls received	2703	2024	2	202402	02/01/24	February 2024
Number of calls received	2895	2024	3	202403	03/01/24	March 2024
Number of calls received	3522	2024	4	202404	04/01/24	April 2024
Number of calls received	4285	2024	5	202405	05/01/24	May 2024
Number of calls received	4469	2024	6	202406	06/01/24	June 2024
Number of calls received	4821	2024	7	202407	07/01/24	July 2024
Number of calls received	4428	2024	8	202408	08/01/24	August 2024
Number of calls received	3581	2024	9	202409	09/01/24	September 2024
Number of calls received	3403	2024	10	202410	10/01/24	October 2024
Number of calls received	3055	2024	11	202411	11/01/24	November 2024

Calender Table built in Query - this is sample but includes from year 2020 till 2025

Date	Year-Global Filter	MONTH	MonthName	Quarter	DayOfWeek	DayName	Index
07/07/2023	2023	7	July	Q3	5	Friday	202307
07/14/2023	2023	7	July	Q3	5	Friday	202307
07/21/2023	2023	7	July	Q3	5	Friday	202307
07/28/2023	2023	7	July	Q3	5	Friday	202307

[v-nuoc-msft · Accepted solution]

Hi @kalkhudary 

I conduct the following tests based on the data you provided and some of the content described in the original article:

Calls Received Variance % = 
VAR Up_Arrow = UNICHAR(9650)
VAR Down_Arrow = UNICHAR(9660)
VAR SelectedYear = SELECTEDVALUE('Calendar Table'[Year-Global Filter])
VAR SelectedCallsReceivedLY = 
    CALCULATE(
        SUM('Table1'[Value]), 
        'Table1'[Year] = SelectedYear - 1, 
        'Table1'[Indicator] = "Number of calls received"
    )
VAR CallsReceivedCurrentYear = 
    CALCULATE(
        SUM('Table1'[Value]), 
        'Table1'[Year] = SelectedYear, 
        'Table1'[Indicator] = "Number of calls received"
    )
VAR Difference = CallsReceivedCurrentYear - SelectedCallsReceivedLY
VAR CallsReceivedPercentage = 
    FORMAT(
        DIVIDE(Difference, SelectedCallsReceivedLY, 0), 
        "0.0%"
    )

RETURN 
    IF(
        Difference < 0, 
        "-" & CallsReceivedPercentage & " " & Down_Arrow & " from LY", 
        "+" & CallsReceivedPercentage & " " & Up_Arrow & " from LY"
    )

Here is the result.

Hope this helps you.

Regards,

Nono Chen

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

[lbendlin]

A proper calendar table is based on a Date column as the primary key, and uses 1:* relationships. Once you have that corrected you can use the standard time intelligence DAX functions.

[kalkhudary]

@lbendlin I do have a date column in The Calender table but I used the index column in the calender table to connect to the other tables while also needing to use the calender table Year to slice. That is of an unual scenario I agree but that was given. Any thought on how to improve the above data model shared based on given scenario and tables provided.