topic Re: Running Total Percentage based on day in DAX Commands and Tips

Running Total Percentage based on day

Brady-Linnebur — Fri, 07 Jun 2024 14:47:41 GMT

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.

Re: Running Total Percentage based on day

rajendraongole1 — Fri, 07 Jun 2024 17:29:00 GMT

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

Re: Running Total Percentage based on day

Brady-Linnebur — Fri, 07 Jun 2024 19:37:05 GMT

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.

Re: Running Total Percentage based on day

Anonymous — Mon, 10 Jun 2024 08:08:33 GMT

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.

Re: Running Total Percentage based on day

Brady-Linnebur — Mon, 10 Jun 2024 17:56:35 GMT

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.

Re: Running Total Percentage based on day

Brady-Linnebur — Mon, 10 Jun 2024 18:42:47 GMT

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.