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

[Joorge_C]

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?

Thanks in advance!!!

[v-cherch-msft]

Hi @Joorge_C

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

[Joorge_C · Accepted solution]

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