cancel
Showing results for
Search instead for
Did you mean:
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
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
https://www.linkedin.com/in/excelenthusiasts/
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.

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)

Member

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

Thanks for your efforts!

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"))`

You may download my workbook from here.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
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,

Share the link from where i can download your PBIX file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

## Helpful resources

Announcements

#### New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)