I have a table with dates (dd.mm.yyyy) and transactional data (sales, discounts, hour rates etc). I have also a separate calendar table. I want to build Power Pivot charts with slicer there user selects “Last month” or "Last 3 Months" or “YTD” and the transactional data in the chart switches based on this selection filtering only data for relevant months. I’ve tried to build via SWITCH but didn’t succeed.
Second point is that in those charts I also want to show growth vs same cumulative period last year based on user selection in slicer. When user selects eg "YTD" chart shows growth YTD vs YTD previous year; if user selects "Last month" the calculation of growth % is based only on last month vs previous year same month.
I believe this should be solved via SAMEPERIODLASTYEAR but I am confused if I have to create measures for each metric (sales, discounts, hour rates etc)? And how to link it with the slicer selection of periods?
Cross posted here.
Hi,
Welcome to our forum!
You can change PivotTable aggregation methods using Slicers as explained here. You do need separate measures for the different periods. I hope that points you in the right direction.
Mynda
Thank you vey much Mynda! This is from what I've started (trying to overcome for the second day..) - but what I want is when user selects "MAT" at the slicer than all the metrics would use only data for periods attributed to MAT. This is a bit different from what is in the tutorial (where it changes calculation method but not filters periods).
I have a sample data with the desired output at the "Destination Tab". Can you please have a look if you have ideas on how to resolve that?
Hi, thanks for sharing your file. First, your calendar table must have consecutive dates if you want to use the time intelligence functions like SAMEPERIODLASTYEAR etc. Your calendar table only has the first date of each month.
In the attached file I've fixed the calendar table and created the measures for the Spend. You need to create additional measures for the discounts.
Note: because your data skips months, the L3M only includes 2 months' worth of data i.e. Sep & Aug.
I hope that gets you started.
Mynda
