YE and YTD p&l calculation

[KimStahl94]

Hi there,

I am trying to display a customized p&l statement as below (shortened extract). The position "Rohertrag" is a sum of "Gesamtleistung and KER". "Wertschöpfung" ist a sum of "Rohertrag" and "Sonst. betriebl. Erlöse". The displayed numbers are correct, but in my YTD measure the subtotals aren't displayed.

	BWA Zwischenergebnis	Case	PL YE	PL YTD
	Gesamtleistung	1	34.941.596 €	11.353.157 €
+	KER	1	-3.114.444 €	-812.144 €
=	Rohertrag	10	31.827.152 €
+	Sonstige betriebl. Erlöse	1	38.732 €	12.066 €
=	Wertschöpfung	11	31.865.885 €

Right now I am using a rather complex (or naive) measure, which, firstly, distinguishes different hierarchy combinations in my p&l statement. The case 1 totals are simply calculated via a column filter, but the totals for the >=10 cases can not be measured via a unique column filter (This is because I want to avoid using a m:n relation between my DIM and FACT Table by mapping my revenue accounts to multiple sub and grand totals).

PL YE = 
SWITCH ( [Case] ,
    10 , CALCULATE ( SUM ( 'FAKT Planwerte'[Planwert] ) , FILTER ( ALL ( 'DIM BWA' ), 'DIM BWA'[BWA Zwischenergebnis] IN { "Gesamtleistung" , "KER" } ) ) ,
    11 , CALCULATE ( SUM ( 'FAKT Planwerte'[Planwert] ) , FILTER ( ALL ( 'DIM BWA' ), 'DIM BWA'[BWA Zwischenergebnis] IN { "Gesamtleistung" , "KER" , "Sonstige betriebl. Erlöse" } ) ) ,
SUM ( 'FAKT Planwerte'[Planwert] ) )

If I then use this YE measures, which works fine, in another YTD Measure, with the latest Month from my Actual data as date filter in the following way, it does not show my grandtotals.

PL YTD = 
CALCULATE 
    ( 
        [PL YE] , 
        FILTER 
            (
                'DIM Datum' ,
                MONTH ( 'DIM Datum'[Datum] ) <= MONTH  ( MAX ('FAKT Istwerte'[Monat] ) )  
            )
    )

I guess my overall approach could be a bit more professional (maybe a calculatedtable, but I have no experience with them), but right now I would be content with the YTD measure delivering correct results based on my YE measure. Any ideas on how to solve this issue?

Regards
KS

[KimStahl94]

Thanks @amitchandak !

I tested it in my previous model and it worked fine.

Regards

KS

[amitchandak · Accepted solution]

@KimStahl94 , for YTD you can use try one of the two can use datesytd

PL YTD =
CALCULATE
(
[PL YE] ,
FILTER
(
'DIM Datum' ,
MONTH ( 'DIM Datum'[Datum] ) <= MONTH ( MAX ('FAKT Istwerte'[Monat] ) )
year ( 'DIM Datum'[Datum] ) <= year ( MAX ('FAKT Istwerte'[Monat] ) )
)
)
or

PL YTD =
CALCULATE
(
[PL YE] ,
FILTER
(
'DIM Datum' ,
MONTH ( 'DIM Datum'[Datum] ) <= MONTH ( MAX ( 'DIM Datum'[Datum] ) )
year ( 'DIM Datum'[Datum] ) <= year ( MAX ( 'DIM Datum'[Datum] ) )
)
)

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date],1,Year), "7/31")