1 of 1 people found this helpful
Find my approach as reference below and stored in attached workbook version 10.5 located in your original post here:
1. 01 D Order Date (Month / Year):
2. 02 D Date: MAKEDATE(INT(MID(str([Order Date (Month / Year) Parameter]),1,4)),INT(MID(str([Order Date (Month / Year) Parameter]),5,2)),1)
3. 01 M CY Sales: if datediff('month',[Order Date],[01 D Date])=0 then [Sales] end
4. 02 M PY Sales: if datediff('month',[Order Date],[01 D Date])=12 then [Sales] end
5. 03 M YOY %: (sum([01 M CY Sales])-sum([02 M PY Sales]))/sum([02 M PY Sales])
6. Drag the required calculated fields & measures to the indicated locations.
it looks good. but can we do this without the parameter ...rather to implement this with a filter?
Yes & No;) if you want to use filter you could define each measure on a separate sheet & bring the 3 sheets together in a dashboard. To my opinion a little cumbersome;)
A lot folks don't like a parameter because it is not updated automatically but you could populate the parameter based on a clipboard action with all dates in the future:)
ok. The parameter is the way to go as far as I know. but considering a dashboard is already done with filter, to avoid re write,
I need to capture the selected date from the date filter and use it in date diff calc for selected month current year to selected month previous year.
can you advice me on how can I achieve this using a filter