Rolling average not filtering

Question

Hi all,&nbsp;I created a measure to calculated to conversion rate from free to paying users in our platform. That measure originally is number of paying transitions/number of free transitions. Then I created a rolling average based on a quick measure of that rate. After some adjustments because of the time intelligence issues, the measure looks like this:Free to Paying conversion rate rolling average = &nbsp;&nbsp;&nbsp;&nbsp;VAR __LAST_DATE = LASTDATE('Calendar'[Date])&nbsp;&nbsp;&nbsp;&nbsp;RETURN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AVERAGEX(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DATESBETWEEN(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Calendar'[Date],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DATEADD(__LAST_DATE, -30, DAY),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;__LAST_DATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE([Free to Paying conversion rate])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)Putting it on a graph by Year and Month it looks quite correct. But since we started in September 2019 the data is somewhat flawed there and showing 100%. I wanted to filter that out with a data slicer or even filter.&nbsp;The problem now is that when using a date slicer or filter (based on Calendar[Date]) the graph becomes rather weird.&nbsp;After filtering or slicing to the second week of September (to not have the 100% showing). The graph looks like this:I'm assuming it has something to do with my Calendar table going back to 2014 and my actual data starting only in September 2019. But I have no idea how to fix it. Anyone that knows how to adjust the formula to only look at information starting from the filter?

alexisolson · Answer

What is the DAX code for your [Free to Paying conversion rate] measure?

(Just FYI, whenever you call a measure within another measure it's implicitly wrapped with CALCULATE. So you don't need to do that manually the way you have it written.)

lbrusselaers · Answer

Thanks for your reply Alexis!&nbsp;The dax code for that measure is:&nbsp;Free to Paying conversion rate = [Total Free To Paying Transitions]/[Total Visitor Single Table]So it's again made up of two measures. Where:Total Free To Paying Transitions = CALCULATE(COUNTROWS(Fact_Account_State_Transition),FILTER(&nbsp;&nbsp;&nbsp;&nbsp;Fact_Account_State_Transition,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;((Fact_Account_State_Transition[TargetAccountStateName] = "Paying")&amp;&amp;(Fact_Account_State_Transition[SourceAccountStateName] = "Free"))))andTotal Visitor Single Table = COUNTROWS(Fact_Account_State_Transition_Visitor)

v-xiaotang · Answer

Hi&nbsp;lbrusselaers&nbsp;
"...&nbsp;September 2019 the data is somewhat flawed there and showing 100%. I wanted to filter&nbsp;..."
do you mean you want to filter data out when data=100%? but how to filter? by removing the date when its data=100%? could you show a sample picture&nbsp;(it can be hand-drawn)?
&nbsp;
&nbsp;
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Rolling average not filtering

3 Replies

Related content

Rolling 12M Average measure

Rolling accumulation

3 Day Rolling Average - DAX

Rolling accumulation

Conditional Rolling Average