Forum Discussion

Snagalapur's avatar
Snagalapur
Helper IV
6 months ago
Solved

Cumulative phasing

Hi All,

 

Kindly suggest how to modify below measure to show cumulative values ie.. P01 = P01, P02 = P01 +P02, P03 = P01 +P02+P03

 

Budget PNL =

VAR LC=

CALCULATE(SUM(FACT_DATA[AMOUNT]), FILTER(FACT_DATA, [CURRENCY_ID]=186 && FACT_DATA [SCENARIO_ID]=2 && year(FACT_DATA [PERIOD_DATE])=[ControlPeriod Current Year]))

 

VAR USD=

CALCULATE(SUM(FACT_DATA[AMOUNT]), FILTER(FACT_DATA, [CURRENCY_ID]=184 && FACT_DATA [SCENARIO_ID]=2&& year(FACT_DATA[PERIOD_DATE])=[ControlPeriod Current Year]))

 

RETURN

IF(SELECTEDVALUE(_Currency[Currency])="Local",LC,

IF(SELECTEDVALUE(_Currency[Currency])="USD Operational",USD))

 

ControlPeriod Current Year = YEAR(ALL(DIM_CONTROL_PERIOD[CURRENT_PERIOD]))

 

my model looks like: 

 

 

 

 

 

 

 

Need Help
  • Snagalapur's avatar
    Snagalapur
    6 months ago

    no problem. I did try below code block to modyfy my formulas and this works 

    Cumulative Sales = 
VAR MaxDate = MAX('DateTable'[Date])
RETURN
CALCULATE(
    SUM('Sales'[Amount]),
    FILTER(
        ALLSELECTED('DateTable'),
        'DateTable'[Date] <= MaxDate
    )
)

     

     

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 months ago

    Hi,

    Is there a month/year associated with the period that you have shown in the column area of the matrix?  Share some data to work with and explain the calculations logic in simple language.