Difference between accounting balances with different closing month

[PhilippeMuniesa]

Hello everyone.

i have been looking for a solution to this problem for a long time, i have tried a lot of filer, sameperiodlastyear, previousyear etc ... solutions but i am still stuck.

I am looking to calculate the difference between three annual balances whose closing date may not be at the end of the same month.

For example

N = 12/31/20

N_1 = 12/31/19
N-2 = 03/31/19

I think to retrieve the value of the balance of the previous balance, the suitable function is DATEADD

In the previous example, it works, except that if for example an account does not exist on 12/31/19, the value of the account on 03/31/19 is not returned

the measurements I use are as follows:

NbMonth:=DATEDIFF(max(Tableau1[Clot_N_1]);max(Tableau1[Clot]);MONTH)

and

MontantN_1Dateadd:=Calculate([Somme_De_Montant]*1;DATEADD(Tableau1[Clot];-[NbMonth];MONTH))

then I would calculate the difference between the two accounting balances as follows

DiffN_N_1:=[Somme_De_Montant]-[MontantN_1Dateadd]

The problem is recovering N-1 to be able to do the difference and% variation calculations.

simple Table for example :

Compte Montant Clot Clot_N_1
101 12 31/12/20 31/12/19
102 13 31/12/20 31/12/19
108 -5 31/12/20 31/12/19
210 8 31/12/20 31/12/19
401 7 31/12/20 31/12/19
108 3 31/12/20 31/12/19
210 1 31/12/20 31/12/19
101 -2 31/12/19 31/03/19
108 10 31/12/19 31/03/19
210 8 31/12/19 31/03/19
108 3 31/12/19 31/03/19
210 2 31/12/19 31/03/19
401 -3 31/12/19 31/03/19
102 6 31/03/19 31/03/18
103 -6 31/03/19 31/03/18
108 -10 31/03/19 31/03/18
210 8 31/03/19 31/03/18
401 -20 31/03/19 31/03/18
210 1 31/03/19 31/03/18
401 -4 31/03/19 31/03/18

Result:

how to match the difference between N and N_1 when the Account does not exist in N

An other problem

another problem, when in the test table I replace 12/31/19 with 09/30/19, the DATEADD measure no longer seems to react as I envision it.

Compte Montant Clot Clot_N_1
101 12 31/12/20 30/09/19
102 13 31/12/20 30/09/19
108 -5 31/12/20 30/09/19
210 8 31/12/20 30/09/19
401 7 31/12/20 30/09/19
108 3 31/12/20 30/09/19
210 1 31/12/20 30/09/19
101 -2 30/09/19 31/03/19
108 10 30/09/19 31/03/19
210 8 30/09/19 31/03/19
108 3 30/09/19 31/03/19
210 2 30/09/19 31/03/19
401 -3 30/09/19 31/03/19
102 6 31/03/19 31/03/18
103 -20 31/03/19 31/03/18
108 -10 31/03/19 31/03/18
210 8 31/03/19 31/03/18
401 -20 31/03/19 31/03/18
210 1 31/03/19 31/03/18
401 -4 31/03/19 31/03/18

Wrong result

Thanks a lot for your help

Philippe Muniesa

[Anonymous]

Go to daxpatterns.com and find the right way of doing what you're trying:

Semi-additive calculations – DAX Patterns

[PhilippeMuniesa]

Hello,

Thanks for this tip, I have viewed and downloaded several models, but I can't find what I'm looking for.
All the models, are based on the dates in rows, while I want the close date in a column, and in the same column, a measure that calculates the difference with the total of the previous year whose total is in the next column.

If you have a model that does this, or that I would not have found on DAX PATTERN I am very interested

Thanks a lot

Ph Muniesa