Financial templates vsLastYear make sameperiod

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 =

CALCULATE([Total Sales],FILTER(ALL('544 Calendar'),'544 Calendar'[MthnNumber] >=MIN('544 Calendar'[MthnNumber])-10000 && '544 Calendar'[MthnNumber] <=MAX('544 Calendar'[MthnNumber])-10000))

AliB

Sam has this video on SamePeriodLastYear which sounds like what you are trying to accomplish.

http://portal.enterprisedna.co/courses/108877/lectures/2404177

Give this a try and see if it helps

Guy

Enterprise%20DNA%20Expert%20-%20Small

Thanks. Unfortunately none of the built in time comparisons work for me as I’m using a non-standard calendar.

AliB,

Can you provide some sample data that you are working with so I can see where you may be having the issue.

Guy

Enterprise%20DNA%20Expert%20-%20Small

I’m usng the financial reporting template

I can’t see how to upload this to here but it is on the website for download.

Just adding some information here.

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.

Sam

AliB,

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.

Guy

Enterprise%20DNA%20Expert%20-%20Small

Thanks. I have the financial template working with my calendar using the formula I mentioned in my original post:

CALCULATE([Total Sales],FILTER(ALL(‘544 Calendar’),‘544 Calendar’[MthnNumber] >=MIN(‘544 Calendar’[MthnNumber])-10000 && ‘544 Calendar’[MthnNumber] <=MAX(‘544 Calendar’[MthnNumber])-10000))

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?

Thanks.

I’ve tried using the formula that you have
Sales LY =

VAR CurrentFinPeriod = SELECTEDVALUE(‘544 Calendar’[Fiscal Period])
VAR CurrentFinYear = SELECTEDVALUE(‘544 Calendar’[Fiscal Year])
return
CALCULATE([Total Sales],
FILTER(ALL(‘544 Calendar’),
‘544 Calendar’[Fiscal Period] = CurrentFinPeriod &&
‘544 Calendar’[Fiscal Year] = CurrentFinYear-1))

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?

I believe a good understanding of ‘context’ is important here.

Check out the grouping of videos here. Especially the evaluation context one.

http://portal.enterprisedna.co/courses/mastering-dax-calculations/lectures/2000594

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.

http://portal.enterprisedna.co/courses/powerbisuperuserworkshop/lectures/1772347

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.

Can’t figure out the moving average. Please help

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.

Sam

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

LY Actuals ('000) = SUMX(FILTER(ALL('Calendar'), 'Calendar'[MthIndex] >=MIN('Calendar'[MthIndex])-12 && 'Calendar'[MthIndex] <=MAX('Calendar'[MthIndex])-12),
    [Actuals ('000)])

image


image
Calendar and Financial Data linked on Date and Months

There’s a bit to setting this all up, but I’ve personally covered it extensively in the below videos.

If you work through these you’ll see exactly how to set everything up so it works seamlessly.

LY Actuals ('000) = SUMX(FILTER(ALL('Calendar'), 'Calendar'[MthIndex] &gt;=MIN('Calendar'[MthIndex])-12 &amp;&amp; 'Calendar'[MthIndex] &lt;=MAX('Calendar'[MthIndex])-12), [Actuals ('000)])

This is also not how you should calculate LY results.

If you see in the demo models you’ll find that it is much simpler by using CALCULATE( …DATEADD.

If you work through the above example you’ll see how this is built up from scratch.

Sam

The calendar runs Jan to Dec so year total is fine Using dateadd but the periods do not report the correct numbers for last year.

If I reference the financial period in the formula it doesn’t then work for quarters

If I calculate using the mthindex -12 syntax all seems to work perfectly

Why are you saying I shouldn’t calculate it like this?

Just trust me on this, you don’t want your formula to be written this way.

Use the simple time intelligence functions as soon as possible.

Check out these starting here.

http://portal.enterprisedna.co/courses/mastering-dax-calculations/lectures/2000648

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

What am I missing here?

Your going to have to upload an example or demo file to clarify the issue your experiencing. This is problem the best way to assist from here.

Thanks