cancel
Showing results for
Did you mean:
bsas Member

## replace 4 measures for cumulative revenue by 1

Hi All,

I have a problem with cration of one measure to replace 4 measures mentioned below. Maybe I'm doing something wrong but I'm just not able to resolve this...

I have 4 measures to calculate cumulative revenue based on category. I need somehow replace them by one to have less calculations.

My measures are:

```a.Won_cumulative = CALCULATE(SUM(Acc_Opp[ss_revenue_base]),   FILTER(Filter(ALLSELECTED(Acc_Opp),Acc_Opp[Opp-ty Status]="1.Won"),     Acc_Opp[ss_closedate] <= MAX(Acc_Opp[ss_closedate])))

a.Forecast_cumulative = CALCULATE(SUM(Acc_Opp[ss_revenue_base]),   FILTER(Filter(ALLSELECTED(Acc_Opp),Acc_Opp[Opp-ty Status] = "2.Forecast"),     Acc_Opp[ss_closedate] <= MAX(Acc_Opp[ss_closedate])))

a.Best Case_cumulative = CALCULATE(SUM(Acc_Opp[ss_revenue_base]),   FILTER(Filter(ALLSELECTED(Acc_Opp),Acc_Opp[Opp-ty Status] = "3.Best Case"),     Acc_Opp[ss_closedate] <= MAX(Acc_Opp[ss_closeday])))

a.Stretch_cumulative= CALCULATE(SUM(Acc_Opp[ss_revenue_base]),   FILTER(Filter(ALLSELECTED(Acc_Opp),Acc_Opp[Opp-ty Status] = "4.Stretch 0-50%"),     Acc_Opp[ss_closedate] <= MAX(Acc_Opp[ss_closedate])))```

Data model example:

 name ss_closedate ss_revenue_base Opp-ty Status opp1 10/10/2017 20000 1.Won opp2 7/23/2017 35000 2.Forecast opp3 5/19/2017 700000 3.Best Case opp4 12/3/2017 10000 1.Won opp5 12/3/2017 10000 4.Stretch opp6 5/19/2017 700000 1.Won opp7 7/23/2017 35000 1.Won opp8 10/10/2017 20000 2.Forecast

Can somebody help me with this?

8 REPLIES 8 v-caliao-msft
Moderator

## Re: replace 4 measures for cumulative revenue by 1

You could ceate another table by using enter table. And then add this column to a slicer, then you can use the measure below to replace your measures.

```Measure =
VAR selecteditem =
IF (
HASONEFILTER ( Slicer[Slicer] ),
LASTNONBLANK ( Slicer[Slicer], 1 ),
BLANK ()
)
RETURN
CALCULATE (
SUM ( Acc_Opp[ss_revenue_base] ),
FILTER (
FILTER ( ALLSELECTED ( Acc_Opp ), Acc_Opp[Opp-ty Status] = selecteditem ),
Acc_Opp[ss_closedate] <= MAX ( Acc_Opp[ss_closedate] )
)
)```

Regards,

Charlie Liao Super User

## Re: replace 4 measures for cumulative revenue by 1

Hi,

Is your year end date 31/12?

Regards,
Ashish Mathur
http://www.ashishmathur.com
bsas Member

## Re: replace 4 measures for cumulative revenue by 1

@Ashish_Mathur

Yes it is 12/31/2017 for 2017. Starting from 2018 I'll use data only for 2018.

bsas Member

## Re: replace 4 measures for cumulative revenue by 1

Thanks, but slicer doesn't suit my requirements. I need to have one measure because I will use it in other measure, which is linked to slicer "type".

`measure = IF(ISFILTERED('revenue model'[type]) &&            DISTINCTCOUNT('revenue model'[type])=1,           [Revenue_Cumulative], SUM('Acc_Opp'ss_revenue_base))`

I cannot use 4 measures in this logic, so I'm looking for one, which will calculate cumulative revenue for each category.

Emaple of chart: (here I use 4 measures one for each cat) bsas Member

## Re: replace 4 measures for cumulative revenue by 1

I finally did it!!! It was so close and oubvious...but for 2 days it fleed from me..

`booking__Cumulative = CALCULATE(SUM(Acc_Opp[ss_revenue_base]),                        FILTER(ALL('Calendar'),'Calendar'[allDates] <=MAX('Calendar'[allDates])))` Super User

## Re: replace 4 measures for cumulative revenue by 1

Hi @bsas,

Your formula will not work correctly in 2018.  When you key in data for January 2018, the cumulative revenue should be only for January 2018 and not from January 2017.  Your formula will accumulate from january 2017.  I believe the correct formula should be:

`=CALCULATE(SUM(Table1[ss_revenue_base]),DATESYTD('calendar'[Date],"31/12"))` Regards,
Ashish Mathur
http://www.ashishmathur.com
bsas Member

## Re: replace 4 measures for cumulative revenue by 1

My calendar (calculate table) is build based on CALENDARAUTO( ) which is linked to my data filtered by year.

I've tried your variant, it works, but in chart it shows same value for each month as cumulative of all year data, which is not correct for my case.  Super User

## Re: replace 4 measures for cumulative revenue by 1

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements #### New Topics Started Badges Coming  