Rolling average not filtering

[lbrusselaers]

Hi all,

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 =

    VAR __LAST_DATE = LASTDATE('Calendar'[Date])

    RETURN

        AVERAGEX(

            DATESBETWEEN(

                'Calendar'[Date],

                DATEADD(__LAST_DATE, -30, DAY),

                __LAST_DATE

            ),

            CALCULATE([Free to Paying conversion rate])

        )

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. The problem now is that when using a date slicer or filter (based on Calendar[Date]) the graph becomes rather weird.

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?

[v-xiaotang]

Hi @lbrusselaers 

"... September 2019 the data is somewhat flawed there and showing 100%. I wanted to filter ..."

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 (it can be hand-drawn)?

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.

[AlexisOlson]

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]

Thanks for your reply Alexis!

The dax code for that measure is: 

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(

    Fact_Account_State_Transition,

        ((Fact_Account_State_Transition[TargetAccountStateName] = "Paying")&&(Fact_Account_State_Transition[SourceAccountStateName] = "Free"))

)

)

and

Total Visitor Single Table = COUNTROWS(Fact_Account_State_Transition_Visitor)