Forum Discussion

Brady-Linnebur's avatar
Brady-Linnebur
Icon for Helper I rankHelper I
2 years ago
Solved

Running Total Percentage based on day

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.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Brady-Linnebur
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.