Forum Discussion
Period Selector - Financial Reports
On my page I wish to show analysis considering current period results only as well as other visuals comparing Year to Date
I use Sameperiodlastyear to create a measure to bring in prior year numbers and YTD to calculate year to date
In the past, I have dropped the transactional dates into a slicer and used the "between" filter to isolate the transaction range I am interested in. For P6, the range selected was 1st Jan to 30th June. This however, is a little clunky and can confuse the user if the period end moves around a little and not a clean normal month end date (which is the trouble I now face in this new company)
I would like to create a slicer where the user can select Period Number, let's say 6 being June and the visuals still work correctly ie, it still pulls all transactions from period 1 through to 6, not just period 6.
I thought I would start out by building a separate period selector table with a "period selector" column and the numbers 1-12 in the columns. The idea is that the user simply selects the period they are interested in - including associated visuals for YTD etc
I`m sure I need some sort of switch statement next to capture the logic, for example, if Period 6 is chosen, display transactional data against periods 1-6, if period 7 is chosen, display data against periods 1-7. But I`m not sure how to write this.
Or maybe there is a better way entirely.
I would welcome any suggestions 🙂
3 Replies
- Ashish_Mathur
Super User
Hi,
Create a calculated column showing the Period (in each row). Create a slicer of period and select a period there. Write these measures
Total = sum(Data[Sales])
Total YTD = calculate([Total],datesytd(calendar[Date]))
Total YTD SPLY = calculate([Total YTD],sameperiodlastyear(calendar[date]))
Hope this helps.
Thank you for your suggestion, another pair of eyes is always appreciated. 🙂 I see what you have done there... but I`m not sure it is going to give me all the flexibility I need.
I`m using D365 F&O and have the period number in my dates table already.
While using the time measures would help, it would prevent me from being able to profile the results month to month. e.g I have visuals showing period performance and YTD performance, but I may also want to profile the results as a matrix where I have period number running across the top.
Is there anyway I can build a logic that says if
Period 1 is selected, show the data from period 1
Period 2 is selected, show the data from period 1 and 2
....
Period 12 is selected, show the data from period 1 through 12?
I would rather build the logic out using references from the dates table itself, rather than time based measures
In the past, I would have achieved this my setting the dateslicer to show data between the start of the year and the end of the period in question.
However, this was a little clunky. It is less acceptable in my current organisation as the period end date does not align with the calendar month end, so risks the user getting the selection wrong.
