60 Day Running Totals with Non Contigous Dates

Hi All,

My ultimate objective is to calculate a 60 day running average and as a first step I am trying to calculate a 60 day running total. But there is a twist. I have data ( fitness data including steps and calories) over 2 years, however, some dates have no data and some others have inconsequential data, both of which I wish to exclude. If you like, an analogy might be a business that generates sales 5 days a week and not at weekends.

In the data table, I have added a column which marks whether the date is useful(1) or not (0). I have created measures called Active Steps and Active Calories, by modifying the base aggregation measures that count only the steps and calories for the useful days.

Using the measure below, I have created another table(called Steps Days) which only includes the useful dates.

Steps Days = 
FILTER(
SUMMARIZE(
    Data,
    'Calendar'[Date],
    Data[Steps],
    Data[Active Days]
    ),
    Data[Active Days] = 1)

Using this, I created a measure to calculate the 60 day running total for steps, but I ended up with rubbish.

60 Day Running Total test =
CALCULATE([Active Steps],
DATESINPERIOD(‘Calendar’[Date],
MAX(‘Steps Days’[Date]),
-60,
DAY))

This screen shot shows the results and what I am working with. The 60D running total is working as it should over the Active Steps Measure, which excludes days with less than 2,000 steps, by adding 60 consecutive days regardless of whether or not any date has useful data. What I am trying to calculate in the 60 Day Running Total Test measure, however, is 60 consecutive useful days of data (Active steps).

Thank you for your assistance in this problem.

The file is attached.

Fitbit.pbix (120.7 KB)

Michael Gordon

@MIchaelGordon Everything before the comma is clear to me but after that I am not able to understand the requirement, can you please explain in more detail?

Sorry, if not entirely clear.

I am trying to calculate a running total on 60 consecutive days of useful data, ie ignoring those days with non-useful data.

I hope that is better.

Thanks Michael Gordon

Hi
I have one solution which you can try.
at first try to create a variable and in that variable filter all non useful days.
then use that variable in your further calculation of running total of 60 consecutive useful days.
I hope it will work.
Best of luck.

@MIchaelGordon See if this works, I checked in the Excel and it gives total of 60 days at each cell in the visual:

Fitbit.pbix (130.7 KB)
data.xlsx (52.8 KB)

Antriksh = 
VAR MaxDate =
    MAX ( 'Calendar'[Date] )
VAR TimePeriod = 60
VAR PopulateDatesWithTotalSteps =
    ADDCOLUMNS ( ALL ( 'Calendar'[Date] ), "@Steps", [Sum of Steps] )
VAR FilterStepsGreaterThan2000 =
    FILTER ( PopulateDatesWithTotalSteps, [@Steps] >= 2000 )
VAR RankDates =
    ADDCOLUMNS (
        FilterStepsGreaterThan2000,
        "@DayRank",
            VAR CurrentDay = [Date]
            VAR Result =
                COUNTROWS (
                    FILTER ( FilterStepsGreaterThan2000, 'Calendar'[Date] <= CurrentDay )
                )
            RETURN
                Result
    )
VAR RankAtCurrentDate =
    MAXX ( FILTER ( RankDates, 'Calendar'[Date] = MaxDate ), [@DayRank] )
VAR FromCurrentDateFilterPast60Days =
    FILTER (
        RankDates,
        'Calendar'[Date] <= MaxDate
            && [@DayRank] <= RankAtCurrentDate
            && [@DayRank] > RankAtCurrentDate - TimePeriod
    )
VAR Result =
    SUMX ( FromCurrentDateFilterPast60Days, [@Steps] )
RETURN
    Result

6 Likes

@AntrikshSharma,

Really nice solution here. But it made me wonder - did you look at this problem and think “okay this is going to take some work, so let’s not spend ANY brainpower naming the measure”? :rofl:

  • Brian
2 Likes

@BrianJ Hahaha, you are absolutely right, I initially named variables T, N, G, P, F. Naming variables/measures takes up so much creativity and time and I don’t want to lose track of all those virtual tables/calculation we have to do in our minds. lol!

2 Likes

I had the same first thoughts as you, so I’m happy it worked.

As for variable naming, I’m not creative either so I am just very verbose and use an entire phrase in CamelCase, like “_CurrentMonthSelectedInSlicer” or whatever. Given the Intellisense in the DAX Editor window, I only need to write it once.

Greg

2 Likes

Hi Antriksh

Thank you very much for your solution. I have tested it and it works.

But I am a struggling newbie and having spent some time looking at the measure, I don’t understand how it works.

I understand what most of the variables are doing, in isolation. What I don’t undertand is what the RankDates variable is doing and how all the bits come together.

Could you please explain your thinking and how it works. This would be greatly appreciated.

Again, thank you very much

Michael Gordon

1 Like

@MIchaelGordon The problem with going back 60 days based on the current date is you would end up at calendar based 60th day, but we want to remove the days that do not match the criteria so it doesn’t makes any sense to traverse back based on calendar, so what is the next best alternative? we have dates! and dates are nothing just a serial number in DAX since 12/30/1899, so we can rank them! and you know we have already filtered Steps so if I go back 60 Ranks back from Today(11/08/2020) I am not going back to September, 08, 2020, I will be at a day that is prior to that depending on where Current Rank - 60 lands us.

You can also see the results yourself by copying the code in a calculated table:

Look at the date at 60th Rank in the below image, if you subtract 60 from 02/11/2019 you would go back to 12/13/2018 which is 60 days based on calendar and not on the rows of our filtered data, and when you go back on the basis of rank you get to 12/03/2018 as required.

3 Likes

Antriksh,

Thank you for your explanation. I can follow the general thrust but have some way to go to follow what’s happening line by line. What you have done will be a great learning piece which I can refer back to to as my experience builds.

Just one other question. Why did you complete the whole expression in two stages (with return twice) rather than with one Return.

I appreciate your help and consideration. A problem I have, and I assume as do many others, is that whilst authors ( I am reading many books including Russo and Ferrari) and Online instructors are very good with the what and how of a DAX expression, the Why tends to be lacking.

Whilst basic expressions are easy to grasp, I find it difficult to understand the processes of more complex expressions and appreciate the explanation of the logic and thought processes behind what the expresssion is seeking to achieve.

Thank you again.

Michael Gordon

First Return statement is Local to the RankDates variable, the Result part of RankDates isn’t available to following statements after “)”, wrote the code like that for readability purpose.

You will get there with practice and time, DAX is one of its own kind so a steep learning curve is expected for everyone. Good Luck.

Hi @MIchaelGordon, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

@MIchaelGordon If you are still interested in a more detailed explanation then I have created this blog that you can read:

Also, a really good question! Thanks.

3 Likes