Forum Discussion

RavM_17's avatar
RavM_17
New Member
3 years ago
Solved

Year on year variance without dates

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       PeriodVar
500 FY22              1 
600 FY22              2 
650 FY22              3 
250 FY22              4 
750 FY23              1150%
130 FY23              222%
560 FY23             386%
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.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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's avatar
    mangaus1111
    Solution Sage
    3 years ago

    Hi RavM_17 ,

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

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    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