Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Creating a year vs. year sales curve for multiple events

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?

1 REPLY 1
sturlaws
Resident Rockstar
Resident Rockstar

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors