Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Guys,
Hope you can help me out here.
My aim is to present an account plan using the correct sums for each level in the account plan. The model is in Danish so I'll translate to English and put the Danish names in ()
I have three tables Calendar (called Bogføringsdato), Postings (Posteringer), Accounts (Kontoplan)
The Accounts table has the full account plan. Each sum is also a row in the table. For every one of these rows, a column specifies the roll-up logic and other columns contains the specific. E.g. when the [Type] column contains the value "Sumfra" it means the roll-up logic should sum from a specific account number to the current account number.
I've actually got it working but without modeling the relationship between the postings and the account plan. Hence I'm working on adding the relationship and altering the DAX to reflect this. This is proving a lot harder than expected.
So far the DAX formula is calcuating the sum correctly over account numbers, but all other filters are ignored.
The Dax for Calculating [Sumfra2 Saldo ÅTD] is
Sumfra2 Saldo ÅTD = IF(MIN('Kontoplan'[Type])="Sumfra" ;CALCULATE(SUM('Posteringer'[Bogført beløb DKK_orig]) ; FILTER(all('Posteringer') ;'Posteringer'[KontoNr]>MIN(Kontoplan[Interval1Fra]) && 'Posteringer'[KontoNr] < MIN(Kontoplan[Kontonr.]))) ;BLANK())The intension is to only do the calculation when the Account from Accounts is of Type="Sumfra". When this is the case sum the posted amount ([Bogført beløb DKK_orig]) sum over all accounts where the account number is between [Interval1Fra] and the current Account number ('Kontoplan'[Kontonr.]).
Sumfra Saldo ÅTD = CALCULATE(SUM('Posteringer'[Bogført beløb DKK_orig]) ; FILTER('Posteringer' ; 'Posteringer'[Måned] = CALCULATE(MAX('Bogføringsdato'[Månedsnr])) && 'Posteringer'[År] = CALCULATE(MAX('Bogføringsdato'[År])) && 'Posteringer'[KontoNr] >= CALCULATE(MIN('Regnskabsopstilling'[Interval1Fra])) && 'Posteringer'[KontoNr] < CALCULATE(MIN('Regnskabsopstilling'[Kontonr.])) && CALCULATE(MIN('Regnskabsopstilling'[Type])="Sumfra") ))Hope you can help me out 🙂
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |