cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## How to compare selected dateranges with calculated period

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"

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: How to compare selected dateranges with calculated period

hi, @bjernersjo

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Highlighted
Regular Visitor

## Re: How to compare selected dateranges with calculated period

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?

Highlighted
Community Support

## Re: How to compare selected dateranges with calculated period

hi, @bjernersjo

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

## Re: How to compare selected dateranges with calculated period

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!

Highlighted
Community Support

## Re: How to compare selected dateranges with calculated period

hi, @bjernersjo

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

## Re: How to compare selected dateranges with calculated period

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.

Highlighted
Community Support

## Re: How to compare selected dateranges with calculated period

hi, @bjernersjo

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors