cancel
Showing results for
Did you mean:
Regular Visitor

## Running sum based on Date-1 and substracting a value based on the Date (working date or not)

Hello All, Im trying to replicate a cumulative formula based on a look up date prior to be able to substract, just like the excel below. Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

## Re: Running sum based on Date-1 and substracting a value based on the Date (working date or not)

Thanks Cherie and all,

I have completed this via two things.

Created a metric to look up the previous week day value

Prev Work Day = CALCULATE(SUM('Dim DateCalendar'[Dim WorkDay.WorkingDay]),DATEADD('Dim DateCalendar'[Date],-1,DAY))

Created a Cumulative Sum formula

Running TSRGFM - IPD2T =
VAR MinDate =CALCULATE(MIN('Dim DateCalendar'[Date]), ALLSELECTED('Dim DateCalendar'))
VAR MaxDate =CALCULATE(MAX('Dim DateCalendar'[Date]), ALLSELECTED('Dim DateCalendar'))
VAR DateRange=FILTER(ALL('Dim DateCalendar'), 'Dim DateCalendar'[Date].[Date]>= MinDate && 'Dim DateCalendar'[Date].[Date] <=MaxDate)
RETURN
[TotSRGoal4Mnth & InspPerD2Tgt]-
SUMX(FILTER(
SUMMARIZE(DateRange,'Dim DateCalendar'[Date],
"Value", MonthViewInspectionDemand[Insp Per Day to target],
"TDATE", MIN('Dim DateCalendar'[Date]))
, [TDATE] <= MAX('Dim DateCalendar'[Date])
),
MonthViewInspectionDemand[Insp Per Day to target]*[Prev Work Day]
)

2 REPLIES 2
Community Support Team

## Re: Running sum based on Date-1 and substracting a value based on the Date (working date or not)

You may try  to create a column to get the values as requested. For example:

```Column  =
SUMX (
FILTER (
Table1,
Table1[Date]
<= EARLIER ( Table1[Date] ) - 1
&& RELATED ( 'Calendar'[Workday] ) = 1
),
Table1[Values]
)```

Regards,

Cherie

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

## Re: Running sum based on Date-1 and substracting a value based on the Date (working date or not)

Thanks Cherie and all,

I have completed this via two things.

Created a metric to look up the previous week day value

Prev Work Day = CALCULATE(SUM('Dim DateCalendar'[Dim WorkDay.WorkingDay]),DATEADD('Dim DateCalendar'[Date],-1,DAY))

Created a Cumulative Sum formula

Running TSRGFM - IPD2T =
VAR MinDate =CALCULATE(MIN('Dim DateCalendar'[Date]), ALLSELECTED('Dim DateCalendar'))
VAR MaxDate =CALCULATE(MAX('Dim DateCalendar'[Date]), ALLSELECTED('Dim DateCalendar'))
VAR DateRange=FILTER(ALL('Dim DateCalendar'), 'Dim DateCalendar'[Date].[Date]>= MinDate && 'Dim DateCalendar'[Date].[Date] <=MaxDate)
RETURN
[TotSRGoal4Mnth & InspPerD2Tgt]-
SUMX(FILTER(
SUMMARIZE(DateRange,'Dim DateCalendar'[Date],
"Value", MonthViewInspectionDemand[Insp Per Day to target],
"TDATE", MIN('Dim DateCalendar'[Date]))
, [TDATE] <= MAX('Dim DateCalendar'[Date])
),
MonthViewInspectionDemand[Insp Per Day to target]*[Prev Work Day]
)