PnL report - Periods vs Dates

Power BI
1

Hi,

I had the following problem:

I have a list of Journal postings (transactions) which have a GL code / Value / Period / Year and Journal date
Now I want to calculate the movements and the YTD movements based on the period and show it in a matrix. The YTD movements are the OpeningBalance of the GL code + the movements.

The problem I am struggling with is that, my matrix has to use the period as columns but I am now not sure how to deal with the YTD measure I would like to calculate as I cannot use the the TOTALYTD function as my data is not filtered on dates but on period. The postings can happen for example in July and august but are still posted to period 1.

Example of the transaction table:

image
image732×632 183 KB

Example of my current model

image
image981×652 81.6 KB

Example of my matrix

image
image798×223 102 KB

The measures are:

  • Movements = Sum(EntryValue)

Now I would like to create the PTD Movements

  • PTD Movements

image
image1234×195 95.1 KB

I use the dates, but thats not correct as it should filter on the periods but not sure how that could work?
I am a bit lost how to reason about the “PTD” movements as its based on the GL periods
so for Period 3, the PTD movements are movements of period 1 + period 2 + period 3.
So when using this in a matrix, it should be clear for the measure which period it has to calculate
the Movements.

Not sure if its all clear what I am after but this is

I tried the following:

Movements PTD = CALCULATE (
[Movements],
Filter(‘Financial transactions’, ‘Financial transactions’[GlPeriod] <= MAX(‘Financial transactions’[GlPeriod])
))

But that still does not give me the correct values.

2

I think the key here is to get the Periods into your date table.

This really is a must in my view. You want any date related analysis to be running from your date table.

If you don’t do this you can’t utilize any of the time intelligence functions and your formula become far more complicated.

Can you get this period logic into your date table? Is it just quarters?

You’ll see with any of the financial reporting examples I’ve run I always integrate any time related filters back into the date table.

Let me know.

3

Hi Sam,

Thanks for the response.
The only thing I can think of, but not sure how to integrate that is the following:

  • The period is based on manually set dates in the accounting program
    ** When the accountant does a month end.
  • I could fetch the start and end date of the period and somehow add that to the date table.
  • Then I could link the GlPeriod, to the GLPeriod in the datetable.

image
image.png801×584 68.4 KB

So somehow I can link the period to the datestable as it is mostly the last day of the month.

The second question was then the following:

Each GLcode has an openingbalance at the start of the year.

eg. 001010 ==> 90K

Then what I want to calculate is the YTD based on this opening balance (which is fixed) and the runningtotal of the movements.

So YTD period 1 = OpeningBalance + RunningValue(Movements)
This was the formula i was using in SSRS and that worked somehow.

Not sure if my running total value will be correct when putting it into a matrix form.

Sorry for the questions but I am sometimes a little lost in how to reason about the dax formulas
especially when linking calculations with slicers later on and see how they influence the calculation.

Thanks a lot.

4

Ok I found a table where the period ends are set.
Any standard way how i could fold that into the Dates table?

The setup is like this.

Name Period end date
Period 1 , 2018-07-31 00:00:00.000
Period 2 , 2018-08-31 00:00:00.000

etc…

I should somehow generate a full table now based on these period end dates.
Not sure how to do it in DAX though at the moment. Will have to think about it.

5

If I understand it correctly, you are trying to populate “Period” in your date table and currently have a file with something as follows:

End Date | Period #

If that’s the case you could use this file as a helper table and use the related function to populate the period in the date table. That could work?

6

Can you get the Period column to have a start and end date.

If you can get these into a table then the logic inside the date table won’t be to difficult.

You should be able to use IF statements or LOOKUPVALUE.

Still the key here is to get this into the date table as mentioned.

Maybe just setting up a supporting table in excel with these dates may be the way to go if it’s difficult to complete any other way.