Forum Discussion

Jennynz's avatar
Jennynz
Regular Visitor
3 years ago

YoY variance for completed months

Hi,

 

I'm very new to the DAX world, so apologies in advance if I'm not wording my question in the best way.

 

I have a report that shows revenue by person and month for various years. Below is a mock up of what I'm working with. 

I need to create a measure at the end of the matrix visual to show YoY variance for completed months (column P). i.e. Jan-Jul 2023 seeing as we haven't completed the month of August. 

I have been able to create 2 measures to work the current year's YoY variance -
the first measure looks up whether the month is a completed month this year:

IsCurrentYearMonth =
IF(
    AND(
        YEAR([Invoice Date].[Date]) = YEAR(TODAY()), 
        [Invoice Date].[Date] <= EOMONTH(TODAY(), -1)    
    ),
    1, 
    0  
)
 
And then another to pull current year revenue:
CurrentYTDRevenue =
CALCULATE(
    SUM('*JobBIlling'[Invoice Amount]),
    FILTER(ALL('*JobBIlling'[Invoice Date].[Date]),
    [IsCurrentYearMonth]=1
    ))
 
What I need is for the YoY variance to work for previous years. I.e. in 2022 the YoY variance shows the variance between 2022 and 2021.
 
Is anyone able to point me in the right direction?
TIA!

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Create a proper date table, there's plenty of articles and videos on doing that, and link it to your fact table. Then you can create a measure like

    YTD Revenue YoY =
VAR ThisYear =
    DATESBETWEEN (
        'Date'[Date],
        DATE ( YEAR ( TODAY () ), 1, 1 ),
        EOMONTH ( TODAY (), -1 )
    )
VAR ThisYearRevenue =
    CALCULATE ( SUM ( '*JobBIlling'[Invoice Amount] ), ThisYear )
VAR LastYearRevenue =
    CALCULATE (
        SUM ( '*JobBIlling'[Invoice Amount] ),
        DATEADD ( ThisYear, -1, YEAR )
    )
VAR Result =
    DIVIDE ( ThisYearRevenue - LastYearRevenue, LastYearRevenue )
RETURN
    Result