Forum Discussion

Nimai123's avatar
Nimai123
Post Patron
6 years ago
Solved

Running total/Cumulative sum

I have created a measure 1 and I want to create a running total measure depending upon the measure 1, I have been through many blogs and links on the internet but not able to get the desired output  ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Nimai123 , Try like

    Running Open Stragglers Low Risk = 
Var a = MINX(ALLSELECTED(Qtr),Qtr[Week Num])
Return
CALCULATE(Sum(bookings_reporting_view[line_item_acv_price_converted]),
FILTER(ALLSELECTED(bookings_reporting_view),
bookings_reporting_view[med_fq_Qtr Name] < MIN(Qtr[Qtr Name])&&
bookings_reporting_view[cld_weekhelp] <= MIN(Qtr[Week Num])&&
bookings_reporting_view[cld_weekhelp] >= a &&

OR(bookings_reporting_view[stage] in {"1 - Discover Opportunity","1 - Renewal Opportunity","2 - Renewal Initiated","3 - Renewal Low Risk","2 - Qualify Opportunity","3 - Validate Solution"}, bookings_reporting_view[stage] in {"4 - Renewal Procurement","5 - Manage Procurement"})
&& bookings_reporting_view[isclosed] = {"0"}))
  • Tahreem24's avatar
    Tahreem24
    6 years ago

    Nimai123 ,

     Try below measure to get your expected result:

     
    Running Stragglers Low Risk=
    Var Res = CALCULATE(sum(bookings_reporting_view[line_item_acv_price_converted]),
    FILTER(ALLSELECTED(bookings_reporting_view),
    bookings_reporting_view[med_fq_Qtr Name] < MIN(Qtr[Qtr Name]) &&
    bookings_reporting_view[cld_weekhelp] <=MIN(Qtr[Week Num]) &&
    bookings_reporting_view[cld_weekhelp] >= (CALCULATE(MIN(Qtr[Week Num]),FILTER(ALLSELECTED(Qtr),Qtr[Week Num]<=MIN(Qtr[Week Num]))))
    && bookings_reporting_view[stage] in {"1 - Discover Opportunity","1 - Renewal Opportunity","2 - Renewal Initiated","3 - Renewal Low Risk","2 - Qualify Opportunity","3 - Validate Solution" } &&
    bookings_reporting_view[isclosed] = {"0"}))
    RETURN Res
     
     
     
     
amitchandak's avatar
amitchandak
Super User
6 years ago

Nimai123 , I done with Qtr Rank and week No. Week no, you use you calc

Start of Year = STARTOFYEAR(Dates[Date],"1/31")
Qtr No = "Q"& QUOTIENT(DATEDIFF(Dates[Start of Year], Dates[Date],MONTH),3)+1
Strat of Qtr = date(year(Dates[Start of Year]), month(Dates[Start of Year])+Dates[Add Qtr],1)
Add Qtr = QUOTIENT(DATEDIFF(Dates[Start of Year], Dates[Date],MONTH),3)*3
FW in Quarter = Your week formula

 

Qtr = CALCULATE([measure], FILTER(ALL(Dates), Dates[Qtr Rank] =max(Dates[Qtr Rank]) && Dates[FW in Quarter] = Max(Dates[FW in Quarter])))
Qtr Running Total New = CALCULATE([measure],, FILTER(ALL(Dates), Dates[Qtr Rank] =max(Dates[Qtr Rank]) && Dates[FW in Quarter] <= Max(Dates[FW in Quarter])))

 

LQ = CALCULATE([measure], FILTER(ALL(Dates), Dates[Qtr Rank] =max(Dates[Qtr Rank])-1 && Dates[FW in Quarter] = Max(Dates[FW in Quarter])))
LQ Running Total New = CALCULATE([measure],, FILTER(ALL(Dates), Dates[Qtr Rank] =max(Dates[Qtr Rank])-1 && Dates[FW in Quarter] <= Max(Dates[FW in Quarter])))

amitchandak's avatar
amitchandak
Super User
6 years ago

Nimai123 , Try like

Running Open Stragglers Low Risk = 
Var a = MINX(ALLSELECTED(Qtr),Qtr[Week Num])
Return
CALCULATE(Sum(bookings_reporting_view[line_item_acv_price_converted]),
FILTER(ALLSELECTED(bookings_reporting_view),
bookings_reporting_view[med_fq_Qtr Name] < MIN(Qtr[Qtr Name])&&
bookings_reporting_view[cld_weekhelp] <= MIN(Qtr[Week Num])&&
bookings_reporting_view[cld_weekhelp] >= a &&

OR(bookings_reporting_view[stage] in {"1 - Discover Opportunity","1 - Renewal Opportunity","2 - Renewal Initiated","3 - Renewal Low Risk","2 - Qualify Opportunity","3 - Validate Solution"}, bookings_reporting_view[stage] in {"4 - Renewal Procurement","5 - Manage Procurement"})
&& bookings_reporting_view[isclosed] = {"0"}))
  • Tahreem24's avatar
    Tahreem24
    Super User
    6 years ago

    Nimai123 ,

     Try below measure to get your expected result:

     
    Running Stragglers Low Risk=
    Var Res = CALCULATE(sum(bookings_reporting_view[line_item_acv_price_converted]),
    FILTER(ALLSELECTED(bookings_reporting_view),
    bookings_reporting_view[med_fq_Qtr Name] < MIN(Qtr[Qtr Name]) &&
    bookings_reporting_view[cld_weekhelp] <=MIN(Qtr[Week Num]) &&
    bookings_reporting_view[cld_weekhelp] >= (CALCULATE(MIN(Qtr[Week Num]),FILTER(ALLSELECTED(Qtr),Qtr[Week Num]<=MIN(Qtr[Week Num]))))
    && bookings_reporting_view[stage] in {"1 - Discover Opportunity","1 - Renewal Opportunity","2 - Renewal Initiated","3 - Renewal Low Risk","2 - Qualify Opportunity","3 - Validate Solution" } &&
    bookings_reporting_view[isclosed] = {"0"}))
    RETURN Res