Financial templates vsLastYear make sameperiod


#4

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


#5

I’m usng the financial reporting template


#6

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


#7

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


#8

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


#9

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?


Cumulative Sales TD (non std calendar)
#10

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?


#11

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

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

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


#12

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


#13

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


#14

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


#15

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.


#16

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


#17

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?


#18

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.


#19

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?


#20

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


#21

Dummy-Data-File.pbix (156.8 KB)
Hi Sam
Here’s a sample project to explain why I don’t believe I can use inbuilt time intelligence. Great if you can prove me wrong - hope you can :smile:

There is also another question within this file relating to banding - It’s all setup and working except for one column. Can you take a look at that as well please. More detail is in the file.

Thanks for your help so far


#22

I’ve had a look through the file,

Now that I understand the scenario in more detail, yes the time intelligence formulas would not work here.

One thing…definitely start formatting your formulas better. The sooner you do this the quicker you will understand what DAX is doing within a formula.

For example I made some simple adjustments below.

image

Also placing information into a table is the best way to understand what is happening at every single results (I do this always). This way I can see the exact context on the calculation

image

It seems like this formula is doing what you need then so you are all good.

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

I look into other ways to do something similar but this currently looks optimal.

You could use something like the below but not neccessary

Order LY = 
VAR CurrentYr = SELECTEDVALUE( 'Calendar'[YearIndex] )
VAR CurrentMth = SELECTEDVALUE( 'Calendar'[Fiscal Period] )

RETURN
SUMX(
    FILTER( ALL( 'Calendar' ),
        'Calendar'[Fiscal Period] = CurrentMth &&
        'Calendar'[YearIndex] = CurrentYr - 1 ),
    [Total Orders] )

Regarding the banding question…can you please place this in another thread.

Check out all the tips for using the forum below.


closed #23