Running total/Cumulative sum

[Nimai123]

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

@amitchandak 

[amitchandak · Accepted solution]

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

[amitchandak]

@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 · Accepted solution]

@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 · Accepted solution]

@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

[Greg_Deckler]

@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]

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