Forum Discussion

lboldrino's avatar
lboldrino
Icon for Resolver I rankResolver I
4 years ago
Solved

Cumulative Revenue by Plan

Hier ist the Request:

if month > Current month -> Actaul Value else plan Value

 
How can i write my code correct to have a cumulative value about this Request:

 

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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])))

      • lboldrino's avatar
        lboldrino
        Icon for Resolver I rankResolver I
        4 years ago

         

        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]

  • lboldrino's avatar
    lboldrino
    Icon for Resolver I rankResolver I
    4 years ago

    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 😞