Measure for Last Year Comparison

In our accounts system transactions are allocated to an accounting year which is the year of the next financial year end. They are also allocated to an accounting period from 01 to 13.

We use a measure to calculate the current year’s profit and loss account.

P & L CY = 
CALCULATE(SUM( NlTransactionsPosted[DrCrAmount] ) * -1, 
    FILTER( NominalCodeGroups, NominalCodeGroups[TypeCode] <= "5"))

This works well.

I want to create a measure to do a comparison with the same accounting period in the previous financial year.

We have looked at DATEADD and SAMEPERIODLASTYEAR but they don’t work because the accounting period allocated is not necessarily related to the transaction date.

I would appreciate any thoughts.

Paul

1 Like

Hello @PaulBoyes,

I think the reason for not working DATEADD or SAMEPERIODLASTYEAR is that you probably referenced Column [TypeCode] from NominalCodeGroups table and these tww functions work with a Date column from a Calendar Table.
You could customize your Calendar Table adding the TypeCode column based on Dates and try using the Date column from there.

My first resource when needing additional info on any DAX function is www.dax.guide
Details for SAMEPERIODLASTYEAR are here: https://dax.guide/sameperiodlastyear/

For Custom Time Intelligence you can find additional details here or here.
There is also a very nice article on daxpatterns.com under Time patterns here

In all cases the most important thing is to have a Date / Calendar Table

Hope it helps!

If you liked my solution please give it a thumbs up :+1:.

If I did answer your question please mark my post as a solution :white_check_mark:.

Thank you !

Cristian

Hello Christian

Thank you very much for your reply. Unfortunately I cannot see the solution but this almost certainly my problem, not yours.

I have attached the pbix file so that the exact nature of the problem can be seen.

The report that I am working on is on the Profit & Loss Account tab. The DAX measures are in the P & L measures section.

The issue with the accounting year is that, for example, the year ending March 2021 then all transactions in that financial year from April 1st 2020 to March 31st 2021 would have the accounting year 2021.

Account periods will be in the range 00 to 13. Period 00 contains opening balances and periods 01 to 13 allows for some businesses to work on 4 week periods rather than months so therefore there are 13 of them.

You will see that the P & L CY measure is very simple. Al I am looking to correct is the P & L LY measure which should contain the same period in the previous financial year.

I hope this makes sense.

Best regards

Paul

Accounts.pbix (1.2 MB)

Hi @PaulBoyes, Enterprise DNA have contents that could help with your concern, here are some similar topics covered in the forum, kindly check them below. Thanks




1 Like