On the page which has Item Q1 Q2 Q3 Q4 Annual Totals…
When vsLastYear is selected is there a way to limit the calculation so that it only shows the data relevant to the data that is in the current year. Eg Jan and Feb populate the Actuals total for the same period last year
I’m working on a financial calendar that always starts on Jan 1 with 4 and 5 week periods. To get the LY figures I’m using for eg:
Total Sales LY =
The financial reporting template will not really work with a non standard date table as no time intelligence functions with calculate as they should.
Non financial reports need different formulas. A lot of the same concepts in the financial reporting resources will still be fine but all main formulas will have to be different.
Please see the below links on how to work out time intelligence with non standard calendars in Power BI
These will provide all the ideas you’ll need to solve for the same results based on the table that you have.
Sorry I didn’t get back to you yesterday but I didn’t see your replies until this morning - guess it’s due to the different time zones we all exist in.
Sam has answered this much better than I can.
If what he says doesn’t help please get back to me and I’ll see what I can do.
What I can’t work out how to do is when there is no data for the current year how to make it not show a difference when having the vsLastYear option checked.
I notice that the model on the website also has this “feature” …… with a standard calendar. Is that because it can’t be done? Some sort of IF statement somewhere?
but this only appears to work when fiscal period is part of the slicer where as my formula appears to work regardless of what I have chosen in the slicer. Am I missing something here?
When you make a selection with your slicer, you have to understand which results are in the current context. This is what will be picked up in the SELECTEDVALUE part.
Honestly though this doesn’t look to me like a non standard calendar. You can still have financial period and financial years within a normal calendar. Non standard calendar usually refer to working with weeks mainly.
I think you have some review to complete of some basic data modeling and setup tips as to me you should just be utilizing a normal date table and making this much simpler on yourself.
I have a calendar table which has a mthindex and qtrindex. The days that belong to each fiscal period change each year hence the non-std. the year always starts 1 Jan and always ends 31 Dec
If I use standard time intelligence then the last year figures are incorrect. I realise that the -10000 doesn’t work hence the new mthindex column meaning I can go back -12 to get last year.
Still even with that scenario you should have the full dates (every date) within your date table.
If the fiscal period is different then you just need to adjust that particular column to line up how you need it.
There is basically no way in proceeding with what you have. You are making your life about 10 times more difficult than it needs to be.
All that is required is a simple setup of a date table once and then you will be set for everything you do post this. Everything will be so easy and more plug and play this way.
I have a date table! All the columns are in there that’s how I get the prior year to work but it doesn’t if I use the built in time intelligence ……
Here is what I have for prior year
I want to trust you But you seem to be ignoring the fact that the standard time intelligence doesn’t give the correct answers …
Unless I’m missing something?
For eg. Using the SAMEPERIODLASTYEAR function in a matrix by Year and Fiscal Period gives the WRONG answer because the dates that make up a Period differ year on year - they are not regular calendar months
