Dynamic comparison for changing time period

[Senya]

Hello,

At first, let me tell you about the data I'm working with. It's web data from Google Analytics for the last 6-8 weeks so it's got I've got dates, values, and lots of dimensions.Here's what I'm trying to do with it.

I would like to compare the value sum for a specific period with the corresponding period. BUT! The normal parallel period formulas are not applicable in my case (or maybe I'm wrong and I can use those somehow) because I need the following logic in my comparison: if less then or equal to the seven days are chosen in filters than compare with the vales seven days ago (so if two days are chosen compare to the two days week ago) if more than seven days are chosen compare to the previous period of the same size (so if 13 days are chosen compare to the 13 days directly before) Plus I would like this measure to be responsive to filtering on the dimension side. I need a percentage difference but I m currently working on the measure that can at least return to me the value based on the logic above.

Here's what I've got so far (below). I've looked up this solution here and to be honest I understand only half of it.

The problem here is that when I'm trying to add all those dimensions (the bottom part of the solution) to be considered in this measure the calculation became super slow and not really usable.

I'm asking the community to help me optimize this script or maybe come up with the different solution to my problem.

Lots of thanks in advance!

Sessions Past Period = 
CALCULATE(SUM (ci_dashboard_v7[Sessions]), FILTER ( ALL ( ci_dashboard_v7 ),  
COUNTROWS (FILTER ( ci_dashboard_v7, EARLIER ( ci_dashboard_v7[Date] ) = DATEADD (  ci_dashboard_v7[Date],IF(DISTINCTCOUNT(ci_dashboard_v7[Date])<7,-7,DISTINCTCOUNT(ci_dashboard_v7[Date])), DAY )
&& ci_dashboard_v7[WMC_partner_lvl1]=EARLIER(ci_dashboard_v7[WMC_partner_lvl1])
&& ci_dashboard_v7[WMC_partner_lvl2]=EARLIER(ci_dashboard_v7[WMC_partner_lvl2])
&& ci_dashboard_v7[Campaign]=EARLIER(ci_dashboard_v7[Campaign])
&& ci_dashboard_v7[cd003]=EARLIER(ci_dashboard_v7[cd003])
&& ci_dashboard_v7[cd135]=EARLIER(ci_dashboard_v7[cd135])
&& ci_dashboard_v7[Channel]=EARLIER(ci_dashboard_v7[Channel])
&& ci_dashboard_v7[Channel_simple]=EARLIER(ci_dashboard_v7[Channel_simple])
&& ci_dashboard_v7[Date]=EARLIER(ci_dashboard_v7[Date])
&& ci_dashboard_v7[Country]=EARLIER(ci_dashboard_v7[Country])
&& ci_dashboard_v7[Domain]=EARLIER(ci_dashboard_v7[Domain])
&& ci_dashboard_v7[Week]=EARLIER(ci_dashboard_v7[Week])
&& ci_dashboard_v7[Week_day]=EARLIER(ci_dashboard_v7[Week_day])
&& ci_dashboard_v7[Year_current]=EARLIER(ci_dashboard_v7[Year_current])
&& ci_dashboard_v7[Week_current]=EARLIER(ci_dashboard_v7[Week_current])
&& ci_dashboard_v7[Week_before_current]=EARLIER(ci_dashboard_v7[Week_before_current])
&& ci_dashboard_v7[is_CRM]=EARLIER(ci_dashboard_v7[is_CRM])
&& ci_dashboard_v7[WMC_tm]=EARLIER(ci_dashboard_v7[WMC_tm])
&& ci_dashboard_v7[Platform]=EARLIER(ci_dashboard_v7[Platform])
&& ci_dashboard_v7[Device]=EARLIER(ci_dashboard_v7[Device])
&& ci_dashboard_v7[OS]=EARLIER(ci_dashboard_v7[OS])
&& ci_dashboard_v7[WMC_type]=EARLIER(ci_dashboard_v7[WMC_type])
&& ci_dashboard_v7[Source_Accengage_Pigeon]=EARLIER(ci_dashboard_v7[Source_Accengage_Pigeon])
&& ci_dashboard_v7[WMC_tool]=EARLIER(ci_dashboard_v7[WMC_tool])
&& ci_dashboard_v7[WMC_full]=EARLIER(ci_dashboard_v7[WMC_full])
&& ci_dashboard_v7[Source]=EARLIER(ci_dashboard_v7[Source])
))))

[v-jiascu-msft · Accepted solution]

Hi @Senya,

I wounder if the measure below is what you want. Please check out the demo in the attachment. If this isn't what you want, please point out the logic.

Measure =
VAR selectedDays =
    CALCULATE ( COUNT ( DimDate[Datekey] ), ALLSELECTED ( DimDate[Datekey] ) )
VAR minDate =
    CALCULATE ( MIN ( 'DimDate'[Datekey] ), ALLSELECTED ( 'DimDate'[Datekey] ) )
RETURN
    IF (
        selectedDays <= 7,
        CALCULATE (
            SUM ( FactSales[SalesQuantity] ),
            DATESINPERIOD ( 'DimDate'[Datekey], minDate - 7, - selectedDays, DAY )
        ),
        CALCULATE (
            SUM ( FactSales[SalesQuantity] ),
            DATESINPERIOD ( 'DimDate'[Datekey], minDate - 1, - selectedDays, DAY )
        )
    )

Best Regards,

Dale

[Senya]

Hey guys!

At first, let me tell you about the data I'm working with. It's web data from Google Analytics for the last 6-8 weeks so it's got I've got dates, values, and lots of dimensions.

Here's what I'm trying to do with it.

And I would like to compare the value sum for a specific period with the corresponding period. BUT! the normal parallel period formulas are not applicable in my case (or maybe I'm wrong and I can use those somehow) because I need the following logic in my comparison:

• if less then or equal to the seven days are chosen in filters than compare with the vales seven days ago (so if two days are chosen compare to the two days week ago)
• if more than seven days are chosen compare to the previous period of the same size (so if 13 days are chosen compare to the 13 days directly before)

Plus I would like this measure to be responsive to filtering on the dimension side.

I need a percentage difference but I m currently working on the measure that can at least return to me the value based on the logic above.

Here's what I've got so far (below). I've looked up this solution here and to be honest I understand only half of it.

The problem here is that when I'm trying to add all those dimensions (the bottom part of the solution) to be considered in this measure the calculation became super slow and not really usable.

I'm asking the community to help me optimize this script or maybe come up with the different solution to my problem.

Lots of thanks in advance!

Sessions Past Period = 
CALCULATE(SUM (ci_dashboard_v7[Sessions]), FILTER ( ALL ( ci_dashboard_v7 ),  
COUNTROWS (FILTER ( ci_dashboard_v7, EARLIER ( ci_dashboard_v7[Date] ) = DATEADD (  ci_dashboard_v7[Date],IF(DISTINCTCOUNT(ci_dashboard_v7[Date])<7,-7,DISTINCTCOUNT(ci_dashboard_v7[Date])), DAY )
&& ci_dashboard_v7[WMC_partner_lvl1]=EARLIER(ci_dashboard_v7[WMC_partner_lvl1])
&& ci_dashboard_v7[WMC_partner_lvl2]=EARLIER(ci_dashboard_v7[WMC_partner_lvl2])
&& ci_dashboard_v7[Campaign]=EARLIER(ci_dashboard_v7[Campaign])
&& ci_dashboard_v7[cd003]=EARLIER(ci_dashboard_v7[cd003])
&& ci_dashboard_v7[cd135]=EARLIER(ci_dashboard_v7[cd135])
&& ci_dashboard_v7[Channel]=EARLIER(ci_dashboard_v7[Channel])
&& ci_dashboard_v7[Channel_simple]=EARLIER(ci_dashboard_v7[Channel_simple])
&& ci_dashboard_v7[Date]=EARLIER(ci_dashboard_v7[Date])
&& ci_dashboard_v7[Country]=EARLIER(ci_dashboard_v7[Country])
&& ci_dashboard_v7[Domain]=EARLIER(ci_dashboard_v7[Domain])
&& ci_dashboard_v7[Week]=EARLIER(ci_dashboard_v7[Week])
&& ci_dashboard_v7[Week_day]=EARLIER(ci_dashboard_v7[Week_day])
&& ci_dashboard_v7[Year_current]=EARLIER(ci_dashboard_v7[Year_current])
&& ci_dashboard_v7[Week_current]=EARLIER(ci_dashboard_v7[Week_current])
&& ci_dashboard_v7[Week_before_current]=EARLIER(ci_dashboard_v7[Week_before_current])
&& ci_dashboard_v7[is_CRM]=EARLIER(ci_dashboard_v7[is_CRM])
&& ci_dashboard_v7[WMC_tm]=EARLIER(ci_dashboard_v7[WMC_tm])
&& ci_dashboard_v7[Platform]=EARLIER(ci_dashboard_v7[Platform])
&& ci_dashboard_v7[Device]=EARLIER(ci_dashboard_v7[Device])
&& ci_dashboard_v7[OS]=EARLIER(ci_dashboard_v7[OS])
&& ci_dashboard_v7[WMC_type]=EARLIER(ci_dashboard_v7[WMC_type])
&& ci_dashboard_v7[Source_Accengage_Pigeon]=EARLIER(ci_dashboard_v7[Source_Accengage_Pigeon])
&& ci_dashboard_v7[WMC_tool]=EARLIER(ci_dashboard_v7[WMC_tool])
&& ci_dashboard_v7[WMC_full]=EARLIER(ci_dashboard_v7[WMC_full])
&& ci_dashboard_v7[Source]=EARLIER(ci_dashboard_v7[Source])
))))

[v-jiascu-msft]

Hi @Senya,

Did it help? Can you mark it as an answer?

Best Regards,

Dale