Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!!
Solved! Go to 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]
)
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
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]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |