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

Forum Discussion

Frequent Visitor

8 years ago

Solved

Dynamic comparison for changing time period

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

Need Help

v-jiascu-msft

8 years ago

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

Dynamic comparison for changing time period.pbix535 KB

3 Replies

v-jiascu-msft

Microsoft Employee

8 years ago

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

Dynamic comparison for changing time period.pbix535 KB

Senya

Frequent Visitor

8 years ago

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
Microsoft Employee
8 years ago
Hi Senya,

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

Best Regards,

Dale

Forum Discussion

Dynamic comparison for changing time period

3 Replies

Related content

Dynamic column comparison of last column

Comparison Chart

Dynamic comparison of values

BI Tools Comparison

Calculate Year over Year comparison dynamically