cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vamshikrishna89 Frequent Visitor
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
Community Support Team

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

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,

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
Community Support Team

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

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,

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.
vamshikrishna89 Frequent Visitor
Frequent Visitor

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

Thanks for your quick response Smiley Happy
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

 

Community Support Team
Community Support Team

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

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

1.png

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.
vamshikrishna89 Frequent Visitor
Frequent Visitor

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

Hi 

 

 

 

Community Support Team
Community Support Team

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

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,

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.
vamshikrishna89 Frequent Visitor
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..