Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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")
@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")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |