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 =
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)