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 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.
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.
If I did answer your question please mark my post as a solution.
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.
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
.
.