Year on year variance without dates

[RavM_17]

Hi,

I need to do a YoY variance based on a period number rather than date. I have a dates query however we report on periods and they move yearly so it's difficult to achieve by using the date functions. My dataset looks like below and I want to acheive the Var column. So it's P1 FY23 vs P1 FY22.

Total Emails Sent	Year	Period	Var
500	FY22	1
600	FY22	2
650	FY22	3
250	FY22	4
750	FY23	1	150%
130	FY23	2	22%
560	FY23	3	86%
560	FY23	4	224%

It would be great if I could do this on a cumulative basis as I intend on having a slicer for period. 

Any help is greatly appreciated.

[Greg_Deckler · Accepted solution]

@RavM_17 You could create a DAX Index column for your table like this and then it should be easy. PBIX is attached below signature.

DAX Index = 
    VAR __TableText = CONCATENATEX(ALL('Table'),[Year] & ":" & [Period],"|",[Year]&[Period],ASC)
    VAR __Count = COUNTROWS(ALL('Table'))
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1, __Count,1),
            "__Year",LEFT(PATHITEM(__TableText,[Value]),5),
            "__Period",RIGHT(PATHITEM(__TableText,[Value]),1) + 0
        )
    VAR __Result = MAXX(FILTER(__Table,[__Year] = [Year] && [__Period] = [Period]),[Value])
RETURN
    __Result

[mangaus1111 · Accepted solution]

Hi @RavM_17 ,

sorry I don´t understand how you have calculated the variance of 150%  in your example

[PaulDBrown]

Another way is to add a date column to your fact table. It may seem an overkill, but it allows for the use of time intelligence functions

Period Date =
VAR _Year =
    RIGHT ( fTable[Year], 2 )
VAR _Period =
    SWITCH ( fTable[Period], 4, 12, 3, 9, 2, 6, 1, 3 )
RETURN
    DATE ( 2000 + _Year, _Period, 1 )

and then create a calendar table

Calendar Table =
ADDCOLUMNS (
    CALENDAR ( MIN ( fTable[Period Date] ), MAX ( fTable[Period Date] ) ),
    "MonthNum", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "dYear", YEAR ( [Date] ),
    "dFYear", "FY" & RIGHT ( YEAR ( [Date] ), 2 ),
    "dPeriod", QUARTER ( [Date] )
)

Then

% vs PY Emails =
VAR _PY =
    CALCULATE ( [Sum emails sent], SAMEPERIODLASTYEAR ( 'Calendar Table'[Date] ) )
RETURN
    DIVIDE ( [Sum emails sent], _PY )

and

% Cumulative vs Cumul PY =
VAR _CY =
    CALCULATE ( [Sum emails sent], DATESYTD ( 'Calendar Table'[Date] ) )
VAR _CumulPY =
    CALCULATE (
        [Sum emails sent],
        FILTER (
            ALL ( 'Calendar Table' ),
            'Calendar Table'[dPeriod] <= MAX ( 'Calendar Table'[dPeriod] )
                && 'Calendar Table'[dYear]
                    = MAX ( 'Calendar Table'[dYear] ) - 1
        )
    )
RETURN
    DIVIDE ( _CY, _CumulPY )

will get you

Sample PBIX attached

[mangaus1111 · Accepted solution]

Hi @RavM_17 ,

see my pbi file. I havee added a column [Year Number] in Power Query and then the calculated column [Var] in DAX.

https://1drv.ms/u/s!Aj45jbu0mDVJiz0wZas9jKH1XGrM?e=GhXI2c

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

[mangaus1111 · Accepted solution]

Hi @RavM_17 ,

sorry I don´t understand how you have calculated the variance of 150%  in your example

[Greg_Deckler · Accepted solution]

@RavM_17 You could create a DAX Index column for your table like this and then it should be easy. PBIX is attached below signature.

DAX Index = 
    VAR __TableText = CONCATENATEX(ALL('Table'),[Year] & ":" & [Period],"|",[Year]&[Period],ASC)
    VAR __Count = COUNTROWS(ALL('Table'))
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1, __Count,1),
            "__Year",LEFT(PATHITEM(__TableText,[Value]),5),
            "__Period",RIGHT(PATHITEM(__TableText,[Value]),1) + 0
        )
    VAR __Result = MAXX(FILTER(__Table,[__Year] = [Year] && [__Period] = [Period]),[Value])
RETURN
    __Result