Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hier ist the Request:
if month > Current month -> Actaul Value else plan Value
Cumulative Umsatz =
var _value = month([Current Day])
var _year = SELECTEDVALUE(dim_opportunity[DeliveryDate].[Year])
var _mon = SELECTEDVALUE(dim_opportunity[DeliveryDate].[MonthNo])
var _umsatz = if(_mon < _value,[Umsatz ProfitCenter],sum(dim_opportunity[Weighted Position]))
Return CALCULATE(_umsatz,
dim_opportunity[DeliveryDate].[Year]= _year,
dim_opportunity[DeliveryDate].[Date] <= MAX(dim_opportunity[DeliveryDate].[Date]))
here is my code, but not correct 😞
thnx
Solved! Go to Solution.
Hi @lboldrino ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create the following measures to get the culmulative values:
ActualPlan =
VAR _curmonth =
MONTH ( TODAY () )
VAR _selmonth =
SELECTEDVALUE ( dim_opportunity[DeliveryDate].[MonthNo] )
VAR _values =
IF (
_selmonth < _curmonth,
CALCULATE ( SUM ( 'dim_opportunity'[Umsatz ProfitCenter] ) ),
CALCULATE ( SUM ( 'dim_opportunity'[Weighted Position] ) )
)
RETURN
_values
Temp =
SUMX (
FILTER (
ALLSELECTED ( 'dim_opportunity' ),
'dim_opportunity'[ProfitCenter]
= SELECTEDVALUE ( 'dim_opportunity'[ProfitCenter] )
&& 'dim_opportunity'[DeliveryDate].[Year]
= SELECTEDVALUE ( 'dim_opportunity'[DeliveryDate].[Year] )
&& 'dim_opportunity'[DeliveryDate].[MonthNo]
<= SELECTEDVALUE ( 'dim_opportunity'[DeliveryDate].[MonthNo] )
),
[ActualPlan]
)
Cumulative Umsatz = SUMX ( VALUES ( dim_opportunity[ProfitCenter] ), [Temp] )
If the above one can't help you get the desired result, please provide some sample data with Text format(exclude sensitive data) and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
How to upload PBI in Community
Best Regards
Hi @lboldrino ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create the following measures to get the culmulative values:
ActualPlan =
VAR _curmonth =
MONTH ( TODAY () )
VAR _selmonth =
SELECTEDVALUE ( dim_opportunity[DeliveryDate].[MonthNo] )
VAR _values =
IF (
_selmonth < _curmonth,
CALCULATE ( SUM ( 'dim_opportunity'[Umsatz ProfitCenter] ) ),
CALCULATE ( SUM ( 'dim_opportunity'[Weighted Position] ) )
)
RETURN
_values
Temp =
SUMX (
FILTER (
ALLSELECTED ( 'dim_opportunity' ),
'dim_opportunity'[ProfitCenter]
= SELECTEDVALUE ( 'dim_opportunity'[ProfitCenter] )
&& 'dim_opportunity'[DeliveryDate].[Year]
= SELECTEDVALUE ( 'dim_opportunity'[DeliveryDate].[Year] )
&& 'dim_opportunity'[DeliveryDate].[MonthNo]
<= SELECTEDVALUE ( 'dim_opportunity'[DeliveryDate].[MonthNo] )
),
[ActualPlan]
)
Cumulative Umsatz = SUMX ( VALUES ( dim_opportunity[ProfitCenter] ), [Temp] )
If the above one can't help you get the desired result, please provide some sample data with Text format(exclude sensitive data) and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
How to upload PBI in Community
Best Regards
here is my Mesaure
Umsatz_ProfitCenter =
var _value = month([Current Day])
var _year = SELECTEDVALUE(dim_opportunity[DeliveryDate].[Year])
var _mon = SELECTEDVALUE(dim_opportunity[DeliveryDate].[MonthNo])
var _pc = SELECTEDVALUE(dim_opportunity[ProfitCenter])
Return
if(_mon < _value,
CALCULATE(sum(dim_Buchungen[Actual Saldo]), dim_Buchungen[Profit-Center Nummer]= _pc && dim_Buchungen[Buchungsperiode Nummer]= _mon && dim_Buchungen[Geschäftsjahr Nummer] = _year),sum(dim_opportunity[Weighted Position])
)
and my CumSum:
Cumulative Umsatz =
CALCULATE(SUMX(values(dim_opportunity[DeliveryDate].[Year]) ,
[Umsatz_ProfitCenter]),
filter(allselected(dim_opportunity[DeliveryDate]),
dim_opportunity[DeliveryDate] <= max(dim_opportunity[DeliveryDate])))
Result:
its not Cumulativ (by profitCenters) and i have no total over month my measure 😞
@lboldrino , Assume actual and plan are measures try a measure like
Cumm Sales = CALCULATE(SUMX(values('Date'[Month Year]) , if(max('Date'[date]) >= eomonth(today(),0) ,[plan], [actual])),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))
dont work 😞
Cumulative Umsatz =
var _value = month([Current Day])
var _year = SELECTEDVALUE(dim_opportunity[DeliveryDate].[Year])
var _mon = SELECTEDVALUE(dim_opportunity[DeliveryDate].[MonthNo])
Return
CALCULATE(SUMX(values(dim_opportunity[DeliveryDate].[Year]) ,
[Umsatz_ProfitCenter]),
filter(allselected(dim_opportunity[DeliveryDate]),
dim_opportunity[DeliveryDate] <= max(dim_opportunity[DeliveryDate])))
How can i cumulative sum by measure [Umsatz_ProfitCenter]
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
70 | |
68 | |
43 | |
34 | |
26 |
User | Count |
---|---|
86 | |
49 | |
45 | |
38 | |
37 |