Year to Date with Year over Year comparison

[TBensen]

Hello,

I'm trying to create a visualisation that looks like the following image:

A few questions about trying to create that.

1.  Can this be done via a matrix visualisation, or should I have each cell be represented as a card and line them up as necessary?

2. I essentially need to compare the current years data with the previous years data to the end of a period, which we'll say ended on 12/15, and we don't any current data to be shown.  I'm pulling in an OData source that contains all of the data necessary to complete the calculations for each row.  I also have indicator fields for each period to indicate if it was a previous full period, meaning I know which period was just completed this year and the previous year.  How would I structure the measure to get the previous years data?  

        My current end of period caluclation looks like this (for recordables):    

Recordables YTD Previous Full Period = CALCULATE(SUM('OII Data'[OSHA Recordable Incidents (Rolled Up)]), FILTER('OII Data', 'OII Data'[End Date] <= TODAY()))

Thanks,

Trevor Bensen

[Anonymous · Accepted solution]

Hi @TBensen ,

Here are some of my personal thoughts on your question:

1. In PowerBI Desktop, it is perfectly possible to use Matrix for your situation.

2. To compare the current year's data with the previous year's data up to a specific period, you can create a measure that filters the data based on the end date and the period indicator. So I think you can try this DAX code:

Recordables YTD Previous Full Period = 
CALCULATE(
    SUM('OII Data'[OSHA Recordable Incidents (Rolled Up)]),
    FILTER(
        'OII Data',
        'OII Data'[End Date] <= DATE(YEAR(TODAY()) - 1, 12, 15) &&
        'OII Data'[Period Indicator] = "Previous Full Period"
    )
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

1. yes, can be done in matrix if you choose "show values on rows"

2. SAMEPERIODLASTYEAR  will help you to shift the filter for a year. It is syntax sugar for DATEADD(xxx,-1,YEAR)

[TBensen]

Hello,

I'm not sure this would work becuase the end of the period for each year changes dates because each period is 4 weeks long.  For example the period this year ended 12/15, but the previous year it would have ended on 12/14.

Thanks,

Trevor

[lbendlin]

If you have custom calendars then use an external reference table that has columns clearly identifying each period, ideally as a number.  So [Fiscal Year]=2025 rather than [Fiscal Year]="FY25"