Line graph to show current year with prior year comparison (only current year on x axis)

[Ericshepdawg]

Hi all, I'm hoping to get some help with structuring a line graph. I would like to have the graph's x axis show all of the current year, and the values associated up until this month, then another line showing the previous year's comparison, but keeping the x axis only for the current year. So it would look like the following:

I've been trying to acheive this with another graph but I can't seem to get it to work. Attached below are the measures i'm using and screenshot of current graph.

Average TII =

AVERAGEX(

    VALUES(_Fact_AR[InvoiceDate]),  // Contextual iteration over unique InvoiceDates in the chart

    VAR FirstDayOfInvoiceMonth = DATE(YEAR(_Fact_AR[InvoiceDate]), MONTH(_Fact_AR[InvoiceDate]), 1)

    VAR RelatedDate = LOOKUPVALUE(_MasterDates[Adjusted First Business Day], _MasterDates[Date], FirstDayOfInvoiceMonth)

    RETURN

    IF(

        SELECTEDVALUE(_MasterDates[IsOnlyCurrentYear]) = 1 &&

        YEAR(_Fact_AR[InvoiceDate]) = YEAR(TODAY()),  // Add the condition for current year

        DATEDIFF(RelatedDate, _Fact_AR[InvoiceDate], DAY),

        BLANK()

    )

)

-------------------------------

Average TII Prior Year =

VAR CurrentYear = MAX(_MasterDates_Disconnected[Year])

RETURN

AVERAGEX(

    VALUES(_Fact_AR[InvoiceDate]),  // Contextual iteration over unique InvoiceDates in the chart

    IF(

        SELECTEDVALUE(_MasterDates[IsOnlyPriorYear]) = 1 &&

        YEAR(_Fact_AR[InvoiceDate]) = YEAR(TODAY()) - 1,  // Add the condition for prior year

        VAR FirstDayOfInvoiceMonth = DATE(YEAR(_Fact_AR[InvoiceDate]), MONTH(_Fact_AR[InvoiceDate]), 1)

        VAR RelatedDate = LOOKUPVALUE(_MasterDates[Adjusted First Business Day], _MasterDates[Date], FirstDayOfInvoiceMonth)

        RETURN

        DATEDIFF(

            RelatedDate,

            _Fact_AR[InvoiceDate],

            DAY),

        BLANK()

    )

)

[Syk]

If you remove the year from your axis to just have months, your lines can indicate the year. Will that work for you?

[Ericshepdawg]

Hi Syk. Unfortunatley I need it to look like the first graph i referenced. Month-Year for current year.

[Syk]

You need to use the calculate function to modify the filter context so you're previous year measure only looks at the previous year. I'm not sure what your data model looks like but if the data is correct in your current measure (just wrong visually), try to wrap the current measure in something like

calculate([Measure here],dateadd('DateTable'[Date],year,-1))

[Ericshepdawg]

Hey Syk. I'm pretty sure it's doing that already. Referencing the PriorYear, and then reflected on the graph as 2022. My intention is to get those values to display on a 2023 timeline. Wonder if disconnected dates is what i'm supposed to look at?

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.