powerbidax

Fixed value for a prior year line chart in Power BI


I have a line and column chart in PowerBI. Columns shows my total sales for each month; i also have a slicer to filter by year. I have a measure to show the average of the full year (fixed, i recieved help on my previously question) fix a value for a line chart in Power BI

But now i want to do the same but for the prior year.

This is my fixed measure for the current year:

FixedCurrent= DIVIDE (
    CALCULATE ( DIVIDE([Sales],50), ALL ( 'Data' ) ),
    CALCULATE ( DISTINCTCOUNT ( 'Calendar'[Month] ), ALL ( 'Calendar' ) )
)

and i already tried something like this but it didnt work:

Planchado Prior Año Anterior = 
    DIVIDE (
        CALCULATE (
            DIVIDE([Sales], 50),
            ALL ( 'Data' ),
            SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
        ),
        CALCULATE (
            DISTINCTCOUNT ( 'Calendar'[month] ),
            ALL ( 'Calendar' ),
            SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
        )
    )

this is an example of what im expecting to have keeping the slicer too:

averges example


Solution

  • Try to use PARALLELPERIOD('Calendar'[Date], -1, YEAR) to shift the data context to the prior year so you can calculate the previous year fixed value :

    FixedPriorYear = 
    DIVIDE (
        CALCULATE ( 
            DIVIDE([Sales], 50), 
            ALL ( 'Data' ),
            PARALLELPERIOD('Calendar'[Date], -1, YEAR)
        ),
        CALCULATE ( 
            DISTINCTCOUNT ( 'Calendar'[Month] ), 
            ALL ( 'Calendar' ),
            PARALLELPERIOD('Calendar'[Date], -1, YEAR)
        )
    )