Forum Discussion
Helper IRunning Total Percentage based on day
Hi Brady-Linnebur ,
You can refer to my test file to learn more details. Here I start my week on Wednesday, you can change the _SELECTSTART part in my code to change the start day.
DimDate = ADDCOLUMNS ( CALENDAR ( DATE ( 2024, 01, 01 ), DATE ( 2024, 12, 31 ) ), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "MonthName", FORMAT ( [Date], "MMMM" ), "WeekDay", WEEKDAY ( [Date], 2 ), "WeekDayName", FORMAT ( [Date], "DDDD" ) )FY Week Start = VAR _SELECTSTART = 3 RETURN IF(DimDate[WeekDay]>=3, DimDate[Date] - [WeekDay] +_SELECTSTART, DimDate[Date] - [WeekDay] - _SELECTSTART - 1)FY WeekDay = VAR _SELECTSTART = 3 RETURN IF(DimDate[WeekDay]>=_SELECTSTART,DimDate[WeekDay] - _SELECTSTART +1, DimDate[WeekDay] + 7 - _SELECTSTART + 1)Measures:
FY WeekDay = VAR _SELECTSTART = 3 RETURN IF(DimDate[WeekDay]>=_SELECTSTART,DimDate[WeekDay] - _SELECTSTART +1, DimDate[WeekDay] + 7 - _SELECTSTART + 1)Running Total = SUMX(FILTER(ALLSELECTED(DimDate),DimDate[Date]<=MAX(DimDate[Date])),[Percentage])Filter = IF(MAX(DimDate[Date])<=TODAY() && MAX(DimDate[FY Week Start]) = CALCULATE(MAX(DimDate[FY Week Start]),FILTER(ALLSELECTED(DimDate),DimDate[Date] = TODAY())),1,0)Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super UserHi Brady-Linnebur Create a measure for this a follows
RunningTotalPercentage =
VAR StartDay = SELECTEDVALUE('Promotions'[StartDay])
VAR DayIndex = WEEKDAY(StartDay, 2) // 2 = Week starts on Monday
VAR Days = {0.09, 0.11, 0.12, 0.13, 0.18, 0.19, 0.18}
VAR TotalDays = COUNTROWS(Days)
VAR DayList = GENERATESERIES(1, TotalDays)
RETURN
SUMX(
DayList,
VAR CurrentIndex = DayIndex + [Value] - 1
VAR AdjustedIndex = IF(CurrentIndex > TotalDays, CurrentIndex - TotalDays, CurrentIndex)
RETURN Days[AdjustedIndex]
)
Change it to your required table and column names .
The above calculatoin works in a way that running total will be calculated in this order, and when Wednesday hits again, it restarts from 0.12.
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Helper IThis did not solve my issue. This instead just repeated the number 28 constantly. I am not sure if it fully reset since I think it is totaling up all of them together.
Hi Brady-Linnebur ,
You can refer to my test file to learn more details. Here I start my week on Wednesday, you can change the _SELECTSTART part in my code to change the start day.
DimDate = ADDCOLUMNS ( CALENDAR ( DATE ( 2024, 01, 01 ), DATE ( 2024, 12, 31 ) ), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "MonthName", FORMAT ( [Date], "MMMM" ), "WeekDay", WEEKDAY ( [Date], 2 ), "WeekDayName", FORMAT ( [Date], "DDDD" ) )FY Week Start = VAR _SELECTSTART = 3 RETURN IF(DimDate[WeekDay]>=3, DimDate[Date] - [WeekDay] +_SELECTSTART, DimDate[Date] - [WeekDay] - _SELECTSTART - 1)FY WeekDay = VAR _SELECTSTART = 3 RETURN IF(DimDate[WeekDay]>=_SELECTSTART,DimDate[WeekDay] - _SELECTSTART +1, DimDate[WeekDay] + 7 - _SELECTSTART + 1)Measures:
FY WeekDay = VAR _SELECTSTART = 3 RETURN IF(DimDate[WeekDay]>=_SELECTSTART,DimDate[WeekDay] - _SELECTSTART +1, DimDate[WeekDay] + 7 - _SELECTSTART + 1)Running Total = SUMX(FILTER(ALLSELECTED(DimDate),DimDate[Date]<=MAX(DimDate[Date])),[Percentage])Filter = IF(MAX(DimDate[Date])<=TODAY() && MAX(DimDate[FY Week Start]) = CALCULATE(MAX(DimDate[FY Week Start]),FILTER(ALLSELECTED(DimDate),DimDate[Date] = TODAY())),1,0)Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Brady-Linnebur
Would mind explaining what the Filter calculation does? On mine they are all 0's and for your file you have it filtered to 1's. I am trying to find out why mine does not have any 1's.
- Brady-Linnebur
It also looks like it is adding up but with some issues. The far right is the running total. For this promo it is supposed to start on Saturday, but it starts adding from Monday. Also when a new week starts, it keeps adding, but I need it to start over again. The far left column is the week start, I need this to say week starting on the start date of promo, which will not always be a monday like it is showing now.