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 

 

Running total Measure would in the form as if it has data in the current week should add with the next value and if there is no data in the next value it should display the same value and move forward.

 

Sample Expected output

 

Measure using to calculating the sales is 

 

Final Open Stragglers Low Risk =
var qr = SELECTEDVALUE('Qtr'[Qtr Name])
var wn = SELECTEDVALUE(Qtr[Week Num])
return
CALCULATE(Sum(bookings_reporting_view[line_item_acv_price_converted]),
bookings_reporting_view[med_fq_Qtr Name] < qr,
bookings_reporting_view[cld_weekhelp] = wn,
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"})
 
Measure using to calculating running total is 
 
Running Open Stragglers Low Risk =

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

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"}))
 
 
Relationship
 
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
     
     
     
     

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Nimai123  - Sample source data would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

     

    But, the general pattern is:

    Cumulative Measure =
  VAR __Current = [Weeknum]
  VAR __Table = 
      SUMMARIZE(
        FILTER('Table',[Weeknum] <= __Current),
        [Weeknum],
        "Measure",[Measure]
      )
RETURN
  SUMX(__Table,[Measure])
    • Nimai123's avatar
      Nimai123
      Post Patron
      6 years ago

      Thanks, Greg

       

      The data is confidential so I will be sending you the sample data file on Personal chat, hope it works.

       

      The result required when selecting FY21-Q1 in the slicer

       

      Greg_Deckler 

  • 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