Creating a year vs. year sales curve for multiple events

[Anonymous]

Our organisation has multiple events at our venue throughout the year. Each event has a different event date and a different sales period e.g. October-March (for a March event), February-September (for a September event, and so on) and we are looking to build a sales curve that can be sliced by any fixture and display sales for its sales period vs. the same time in the previous year.

The issue we are finding is that we slice by event year to choose the event whose sales curve we wish to display, but we also have a transaction date on our x-axis and not all transactions are in the event year. We only have one date table. 

Through various ways we have formed a sales curve that is continuous (for each annual iteration of the event it adds previous years' sales onto the current year and also a variation that drops to 0 on the 1 Jan each year (but with some sales periods spanning October-March this is not ideal). 

Is the community aware of any solutions that would give us our desired sales curve?

[sturlaws]

Hi @Anonymous 

Power BI cannot separate filter context coming from e.g. the transaction date on the x-axis from the filter context coming from year in a slicer.

You will need to create two new tables, each of the tables with a single column:
dimYear = Values('SomeTable[Year])
dimFixture = Values('SomeTable'[Fixture])

Use these columns as slicer-values.

Then write your measures like this

measure =
CALCULATE (
    SUM ( SomeTable[SomeColumn] );
    FILTER ( SomeTable; SomeTable[Fixture] IN dimFixture[ColumnName] )
)

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.