Previous year measure to display on a current year axis

[Ericshepdawg]

Hey all, i'm just looking to have these 2 measures be reflected on the 2023 axis. I'm using a column called [isonlycurrentyear] to reference the 2023 measure. Really not sure what the best approach is... maybe a disconnected table? Any help is much appreciated!

Here are the 2 measures i'm working with

Average TII Current Year =

VAR CurrentYear = SELECTEDVALUE(_MasterDates[IsOnlyCurrentYear])

RETURN

IF(

    CurrentYear = 1,

    [Average TII],  // Replace this with the name of your existing Average TII measure

    BLANK()

)

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

Average TII Previous Year =

VAR CurrentYear = SELECTEDVALUE(_MasterDates[IsOnlyPriorYear])

RETURN

IF(

    CurrentYear = 1,

    [Average TII],  // Replace this with the name of your existing Average TII measure

    BLANK()

)

[Ericshepdawg]

Made a bit of progress but still can't seem to get the year to go from Jan-Dec. Just stops at current.



Here is the updated measure for Average TII - Prior Year:

Average TII PY =

VAR CurrentYear = MAX(_MasterDates_Disconnected[Year])

VAR PriorYear = CurrentYear - 1

VAR AverageTII =

    AVERAGEX(

        VALUES(_Fact_AR[InvoiceDate]),

        IF(

            YEAR(_Fact_AR[InvoiceDate]) = PriorYear,

            DATEDIFF(

                DATE(PriorYear, MONTH(_Fact_AR[InvoiceDate]), 1),  // Start of the prior year's month

                _Fact_AR[InvoiceDate],

                DAY

            ),

            BLANK()

        )

    )

RETURN

IF(

    SELECTEDVALUE(_MasterDates[IsOnlyCurrentYear]) = 1,

    AverageTII,

    BLANK()

)

[Anonymous]

Hi @Ericshepdawg ,

Please update the formula of measure [] as below and check if it can return the expected result...

Average TII PY =
VAR CurrentYear =
    MAX ( _MasterDates_Disconnected[Year] )
VAR PriorYear = CurrentYear - 1
VAR AverageTII =
    AVERAGEX (
        VALUES ( _Fact_AR[InvoiceDate] ),
        IF (
            YEAR ( _Fact_AR[InvoiceDate] ) = PriorYear,
            DATEDIFF (
                DATE ( PriorYear, MONTH ( _Fact_AR[InvoiceDate] ), 1 ),
                // Start of the prior year's month
                DATE ( PriorYear, 12, 31 ),
                //_Fact_AR[InvoiceDate],
                DAY
            ),
            BLANK ()
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( _MasterDates[IsOnlyCurrentYear] ) = 1,
        AverageTII+0,
        BLANK ()
    )

If the above one can't help you, please provide some raw data in the table '_Fact_AR' and '_MasterDates' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Ericshepdawg]

Thanks so much for the solution. It seems very close to working, however the calculation seems to stop working once past the current month. Do you think it's relying on the current year somewhere to create the calculation? See below. 

[Anonymous]

Hi @Ericshepdawg ,

It's a little difficult to find the cause of problem base on the screenshot... Could you please provide some raw data in the table '_Fact_AR' and '_MasterDates' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards