Forum Discussion
Periodic 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:
Snagalapur , If they in the same format they are sortable. You can use visual calculation running sum
Visual Calculations in Power BI- February 2024 Update RUNNINGSUM, RANGE, MOVINGAVERAGE, COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, LAST, PREVIOUS, and NEXT
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
Else have Rank column in your table on YYYYPP (Year period )
Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)
Then you can measure
Cumm = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]<=max(Period[Period Rank])))
Also other measures
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))
2 Replies
Snagalapur , If they in the same format they are sortable. You can use visual calculation running sum
Visual Calculations in Power BI- February 2024 Update RUNNINGSUM, RANGE, MOVINGAVERAGE, COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, LAST, PREVIOUS, and NEXT
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
Else have Rank column in your table on YYYYPP (Year period )
Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)
Then you can measure
Cumm = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]<=max(Period[Period Rank])))
Also other measures
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))Thank you for your time.
I used below sample code to modify and this works
Cumulative Sales = VAR MaxDate = MAX('DateTable'[Date]) RETURN CALCULATE( SUM('Sales'[Amount]), FILTER( ALLSELECTED('DateTable'), 'DateTable'[Date] <= MaxDate ) )
