cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joorge_C Regular Visitor
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?

 

 

Thanks in advance!!!cummulative minus value.png

1 ACCEPTED SOLUTION

Accepted Solutions
Joorge_C Regular Visitor
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
Community Support Team

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

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

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.
Joorge_C Regular Visitor
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]
        )