cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Need To Calculate Cumulative of sales with only 14 Quarters data

ASK: Cumulative of Sales which has to be displayed only for 14 quarters or 14 data points on a line chart?

Problem:

Since cumulative works only by using ALL,AllSelected or AllExcept functions, unable to achieve cumulative for 14 quarters along with other Slicers to work.

Reason: Since we are using ALLSelected with filter of 14 Quarters, I am unable to get rest of the quarters(<14 Quarters data). So Cumulative starts with 14 Quarters if I apply 14 Quarters filter but Cumultaive before 14 Quarters are not added.

Would be greatful if you could help!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Need To Calculate Cumulative of sales with only 14 Quarters data

You may use below measure:

```Measure =
IF (
HASONEVALUE ( Slicer[FY Qty] )
&& MAX ( Table1[Index] ) <= SELECTEDVALUE ( Slicer[Index] )
&& MAX ( Table1[Index] )
> SELECTEDVALUE ( Slicer[Index] ) - 14,
[cumulative]
)
```

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Community Support Team

## Re: Need To Calculate Cumulative of sales with only 14 Quarters data

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Need To Calculate Cumulative of sales with only 14 Quarters data

DATESYTD and SAMEPERIODLASTYEAR doesnt work in my case because I need 14 Quarters data.

I dont have problem with future dates. Problem is that I need 14 Quarters cumulative till this month with Previous quarters(<14 Quarter) sales added.

To be specific FY17 Q2 to FY20 Q2 is the X-Axis and Y-Axis should show cumulative from FY17 Q2 but cumulative should start from FY16Q1 to FY17Q1.

Lets say 291 is the sales till FY17 Q1 and FY17 Q2 is 100, FY Q2 is 5.

In this case, Line Chart should show

X-axis                 =  FY17 Q2, FY17 Q3, FY17 Q4,...

and Y-Axis sales =  391        ,491        ,495

There is also Status Slicer Completed, In progress and Buffer.

291 has completed status till FY17 Q1, FY17 Q2 50 is buffer and completed, FY17 Q2 Completed.

Once we achieve cumulative, Status slicer should also work.

I hope you got picture of the requirement

Community Support Team

## Re: Need To Calculate Cumulative of sales with only 14 Quarters data

You may create an index column for your data by date rank.Create a slicer table.Make sure there is no relationship.Then create a measure to get it.Attached simplified sample file for your reference.

```cumulative =
CALCULATE (
SUM ( Table1[Sales] ),
FILTER ( ALLSELECTED ( Table1 ), Table1[Index] <= MAX ( Table1[Index] ) )
)```
```Measure =
IF (
HASONEVALUE ( Slicer[FY Qty] ),
CALCULATE (
[cumulative],
FILTER (
ALLSELECTED ( Table1[Index] ),
Table1[Index] <= MAX ( Table1[Index] )
&& Table1[Index] <= SELECTEDVALUE ( Slicer[Index] )
&& Table1[Index]
> SELECTEDVALUE ( Slicer[Index] ) - 14
)
)
)
```

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Need To Calculate Cumulative of sales with only 14 Quarters data

Community Support Team

## Re: Need To Calculate Cumulative of sales with only 14 Quarters data

You may use below measure:

```Measure =
IF (
HASONEVALUE ( Slicer[FY Qty] )
&& MAX ( Table1[Index] ) <= SELECTEDVALUE ( Slicer[Index] )
&& MAX ( Table1[Index] )
> SELECTEDVALUE ( Slicer[Index] ) - 14,
[cumulative]
)
```

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Need To Calculate Cumulative of sales with only 14 Quarters data

Thank you So much... That worked like magic... I didnt expect Community would solve my problem... Surprisingly asnwer was super fast from you.

Thanks alot..