cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmodrak Frequent Visitor
Frequent Visitor

sameperiodTHISyear Time Filtering

The Why: So I am building out a visualization that leverages time as a filter/slicer to pick between two points in time. From that filtering/slicer of time I'm looking to see a like for like comparison between what would be most akin to the inverse of the 'SAMEPERIODLASTYEAR' function. 

 

The How: Basically, if I were to pick a date range between 8/1/2017 - 12/31/2017 or 8/1/2015 - 12/31/2015 I would want to have a series of values summed for 8/1/2017 - 12/31/2017 next to a series of values summed for 8/1/2018 - 12/31/2018. Or in the case of the second range, have the slicer tuned to 8/1/2015- 12/31/2015, and also have values summed (in a different location/visual) between 8/1/2018 - 8/1/2018. Basically, no matter the time change be it the year 2017, or 2015, or 2005 (if there were data), I'd want to see a measurement summation between the selected filtered date and the SAMEPERIODTHISYEAR. 

 

Note: The way I want to have the filter used in a way where the users pick the date value for the period in question, and in one chart they see the Selected Period's "burn rate" in one chart, then in a separate chart see the Current Year's "burn rate" in another chart. I've tried a few ways to work through this one, but with the variable of the calculation makes for DATEADD a little complex, because the Current Year is never going to be static during the timeline of the Power BI Report.

3 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: sameperiodTHISyear Time Filtering

Hi @mmodrak,

 

I made one sample for your reference.

 

1. Create a CALENDAR table and create relationship with the fact table as below.

 

Capture.PNG

 

2. Create the measures as below.

 

Current Year's "burn rate" = var mind =CALCULATE(MIN('CALENDAR'[Date]))
var maxd = CALCULATE(MAX('CALENDAR'[Date]))
var thisyearmin = DATE(YEAR(TODAY()),MONTH(mind),DAY(mind))
var thisyearmax = DATE(YEAR(TODAY()),MONTH(maxd),DAY(maxd))
return
CALCULATE(SUM(Table1[burn rate]),FILTER(Table1,Table1[date]>=thisyearmin && Table1[date]<=thisyearmax))
Selected Period's "burn rate" = CALCULATE(SUM(Table1[burn rate]),USERELATIONSHIP('CALENDAR'[Date],Table1[date]))

2.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

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

Re: sameperiodTHISyear Time Filtering

Hi @mmodrak,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case.


Regards,
Frank

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

Re: sameperiodTHISyear Time Filtering

Hey, I really appreciate your help on this, this response got kicked by my spam filter for whatever the reason! 

 

Nevertheless, thank you so much! Happy Holidays! 

3 REPLIES 3
Community Support Team
Community Support Team

Re: sameperiodTHISyear Time Filtering

Hi @mmodrak,

 

I made one sample for your reference.

 

1. Create a CALENDAR table and create relationship with the fact table as below.

 

Capture.PNG

 

2. Create the measures as below.

 

Current Year's "burn rate" = var mind =CALCULATE(MIN('CALENDAR'[Date]))
var maxd = CALCULATE(MAX('CALENDAR'[Date]))
var thisyearmin = DATE(YEAR(TODAY()),MONTH(mind),DAY(mind))
var thisyearmax = DATE(YEAR(TODAY()),MONTH(maxd),DAY(maxd))
return
CALCULATE(SUM(Table1[burn rate]),FILTER(Table1,Table1[date]>=thisyearmin && Table1[date]<=thisyearmax))
Selected Period's "burn rate" = CALCULATE(SUM(Table1[burn rate]),USERELATIONSHIP('CALENDAR'[Date],Table1[date]))

2.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

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

Re: sameperiodTHISyear Time Filtering

Hi @mmodrak,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case.


Regards,
Frank

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

Re: sameperiodTHISyear Time Filtering

Hey, I really appreciate your help on this, this response got kicked by my spam filter for whatever the reason! 

 

Nevertheless, thank you so much! Happy Holidays! 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 377 members 4,226 guests
Please welcome our newest community members: