Forum Discussion
Helper ISeeing Current and Prior year data using a single Year and Month Slicer
Hello,
I am trying to present two tables on teh same page; one with current year data and one with prior year data with a single year slicer and a single month slicer. I have two data tables: one with date information and the second with all of the financial data. The Program data has Contract Name, Contract date, income, etc... and teh tables are joined by Date from the date table and Contract Date from the Program table
I have setup two slicers, one with the year from the date table and one with Month from the Date table. When i filter the page, the two tables on the page show every single contract but only the contracts with the current date selected in the current table and contracts with the date prior to the selected one in the filter have values. the rest do not. If I switch the Year slicer to another year, then the values corresponding to those years are correctly filled in but the entire contract list, the rest with 0 values are still shown. Any ideas about how to have the actual non valid contracts not appear in the tables on the page?
Any ideas?
Thank you...
2 Replies
- lbendlin
Super User
- Use a calendar table as the controlling dimension
- add a field "Same date last year"
- connect the calendar date to the date for the actuals
- connect the new field to the date for the historicals
- use the calendar fields for your slicers.
Alternatively, create measures that shift your selected dates by a year ( for example SAMEPERIODLASTYEAR) to lookup the history table data
Hi sirgseymour
I don't quite understand what you mean “how to have the actual non valid contracts not appear in the tables on the page ” . If you don't want the data whose income is displayed as 0, you can filter out the data whose value is 0 through visual filter .
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
