Running Total Percentage based on day

[Brady-Linnebur]

Hello I need to get a measure/calculated column that does the following.

Here are the values:

Monday = .09 aka 9%

Tuesday = .11

Wednesday = .12

Thursday = .13

Friday = .18

Saturday = .19

Sunday = .18 

This adds up to 100%

Say I have my promotion start on a Wednesday, I need to first start on the 12%, then add 13% etc. all the way till Tuesday which would be the last 11%. Then when Wednesday hits again, I need to restart that running total. If a promotion starts on a Friday, then the first number needs to be 18% then etc until Thursday. Let me know if you need other information also. I am stuck at this point and looking for some help.

[rajendraongole1]

Hi @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!!

[Brady-Linnebur]

This 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.

[Anonymous · Accepted solution]

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 Zhou

If 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.