How to compare selected dateranges with calculated period

[Anonymous]

I need to be able to select one or more periods wich are defined by Start and End-date and then compare activities that happened within those periods to activities that happened during the corresponding periods before.

Here is a pbix-file that shows what Im trying to accomplish and the problem Im not being able to solve:

Example.Select.Periods.v2.pbix

There are two problems I need to solve.

1. When I select Period 1 and 3 I want the same periods to be selected in "Period before".

2. The graph "Period before" should only show Activities that happened during the selected periods in "Period before"

Thanx in advance!

[v-lili6-msft]

hi, @Anonymous

After my test on you pbix, you could try this way:

1. Create a relationship between Period and PeriodBefore by Period

2. Use this formula instead of Period Before NoOfActivities2

Measure = var _table=SUMMARIZE(PeriodBefore,PeriodBefore[Period],"a",[Period before start],"b",[Period before end]) return
calculate( DISTINCTCOUNT( Activity[Activity] ), FILTER (
    CROSSJOIN ( _table, 'Activity' ),
        Activity[Date] >= [a]
    &&  Activity[Date] <= [b]
 ))

Result:

Best Regards,

Lin

[Anonymous]

Thankyou for your reply. But maybe I failed to describe my problem properly. I need to be able to compare the full period to the corresponding period before. That means that if the user selects the periods: monday-wednesday and saturday-sunday I wish to compare that to mon-wednesday and sat-sun the week before. If the user selects mon-wed and tue-thur (periods can overlap) and saturday  (6 days total)  I wish to compare that to the corresponding 6 days before which is tue-fri and sunday.

There are three problems.

First I need to calculate the number of days of the selected periods. (This is not a problem in itself.)

Second I need to create the corresponding periods. I do that in the Periods before table with the correct date as you can see in my example file.

Thirds I need to select only the corresponding periods in Period Before. If the user selects Period 1 and 3 the same periods should be selected in Periods Before. Now the problem is that when I try to do that with TREATAS for instance the calculation of the number of days in the full period is affected. The problem I think is that the first step is applied to the entire selection and when I try to apply that number to the separate rows in this step the row context affects the first calculation and messes it up and I cant figure out how to stop it from doing that.

Finally I need to calculate the Activities in the Periods Before and show that in the graph but that is just the final step and not a problem per se.

Thanx again!

[v-lili6-msft]

hi, @Anonymous

I'm a little confused about your description, 

First I need to calculate the number of days of the selected periods. (This is not a problem in itself.)

the number of days is datediff(min( Period[Start] ), max( Period[End] ), day ) + 1 and It will be affected by period you selected.

The problem I think is that the first step is applied to the entire selection and when I try to apply that number to the separate rows in this step the row context affects the first calculation and messes it up and I cant figure out how to stop it from doing that.

Can you explain your expected output with sample data in pbix?

Best Regards,

Lin

[Anonymous]

Thanx for spending your time on this 🙂

When I select Period 1 and 3 in the slicer the top graph shows the acitivites during the selected periods in the "Selected Periods" graph. That is November 1 to 4 and November 13 to 14.

I want to compare the entire period to the same period before. So.. since the first period is 14 days in total I want to compare that to the 14 days before. Now each selected period should be applied to the period before. So the first 4 days (Period 1) in the "Period before" is October 18 to 21 and the seconde selected period (Period 3) becomes October 30 to 31. The activites from those to "periods before" is what I wish would show up in the "Period before" graph. So... since there is only one activity on october 18 (no acitivities on october 19 to 21) this one should show up in the "Period before" graph and there is one activity on October 31 so that one should also be shown.

If the user selects Period 2, 3 and 4. The entire period is 11 days (November 7 to 17). Then I want to compare that to the 11 days before (October 27 to November 6). Period 2 is four days long which means that activities from October 27 to 30 should show in the "Period before" graph, November 2 to 3 and also November 5 to 6 should also appear in the "Period before" graph. The activity on November 4 should not show up in the "Period before" graph since that is in between the two calculated periods.

I hope this makes it clearer.

[v-lili6-msft · Accepted solution]

hi, @Anonymous

sorry for late reply.

for the first question, you could use these formulas as start Period before and end Period before

Period before start = var length=CALCULATE( [Period Selected lenght] ,ALL(PeriodBefore[Period])) return
MAXX( PeriodBefore, PeriodBefore[Start] - length )

Period before end = var length=CALCULATE( [Period Selected lenght] ,ALL(PeriodBefore[Period])) return
MAXX( PeriodBefore, PeriodBefore[End] - length )

and for the second question, you could use this measure

Measure = var _table=CALCULATETABLE(SUMMARIZE(PeriodBefore,PeriodBefore[Period],"a",[Period before start],"b",[Period before end])) return
calculate( DISTINCTCOUNT( Activity[Activity] ), FILTER (
    CROSSJOIN ( _table, 'Activity' ),
        Activity[Date] >= [Period before start]
    &&  Activity[Date] <= [Period before end]
 ))

Result:

and here is new pbix, please try it.

Best Regards,

Lin

[Anonymous]

Maybe I can add that just creating a relationship between the two lists doesnt work since that affects the calculation of the previous periods start and end date. So another way to solve the same problem would be to "freeze" the filtering on the measures that calculates the dates. I need to be able to use the measure as it is but if I apply it together with an active relationship between Period and PeriodsBefore they will be recalculated. Is there any way to tell a measure to freeze the context at some point?