Need To Calculate Cumulative of sales with only 14 Quarters data

[vamshikrishna89]

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!!!

[v-cherch-msft]

Hi @vamshikrishna89 

Please check if this article could help you.If you need further help,sample data and expected output will be helpful to provide an accurate solution.Please refer to article How to Get Your Question Answered Quickly  to post your data and expected output.

Regards,

[vamshikrishna89]

Thanks for your quick response 
Gone through this article as well before posting here.

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

[v-cherch-msft]

Hi @vamshikrishna89 

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,

[vamshikrishna89]

Hi v-cherch,

Thank you for the quick response...

I looked into your solution which is great which is half way to the solution.

Major problem is that Cumulative is not working for the previous Quarters.

Lets say if i select FY20 Q4 is selected then cumulative should start from FY17 Q1

but data should be displayed from FY17 Q3 as below. And also FY17Q1, FY17Q2 should not be visible in line chart.

Qyarter     Sales

FY17 Q3     85   (FY17 Q1 to FY17 Q2)

FY17 Q4     105 

[v-cherch-msft · Accepted solution]

Hi @vamshikrishna89 

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,

[vamshikrishna89]

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..