Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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"

 

 

Thanx in advance!

1 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:

11.JPG12.JPG

 

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.

View solution in original post

6 REPLIES 6
v-lili6-msft
Community Support
Community Support

hi, @Anonymous

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

1. Create a relationship between Period and PeriodBefore by Period

1.JPG

 

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:

2.JPG

 

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.
Anonymous
Not applicable

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!

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

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.
Anonymous
Not applicable

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.

 

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:

11.JPG12.JPG

 

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.
Anonymous
Not applicable

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?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.