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:
Example of my current model
Example of my matrix
The measures are:
- Movements = Sum(EntryValue)
Now I would like to create the PTD Movements
- PTD Movements
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
Not sure if its all clear what I am after but this is
I tried the following:
Movements PTD = CALCULATE (
Filter(‘Financial transactions’, ‘Financial transactions’[GlPeriod] <= MAX(‘Financial transactions’[GlPeriod])
But that still does not give me the correct values.