cancel
Showing results for
Did you mean:
Frequent Visitor

## Show results before a selected date based on date slicer selection

In image #1, I have selected date range of 3/11/2022 to 12/17/2022 which equates to 282 days. I am looking for calculation that returns the date range for the same number of days that comes immediately before them. What I am trying to achieve ultimately is the ability to do calcuation as shown in image #2.

2 ACCEPTED SOLUTIONS
Super User

Hi,

Assuming the date range is built from a Calendar Table, write these measures

Total = sum(Data[Sales])

Days selected = max(calendar[date])-min(calendar[date])+1

Total 1 = calculate([Total],datesbetween('calendar'[date],min(calendar[date])-[days selected],min(calendar[date])-1))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

You are welcome.  If you wish to show the date range, then write this measure

Measure1 = min(calendar[date])-[days selected]&" - "&min(calendar[date])-1

Regards,
Ashish Mathur
http://www.ashishmathur.com
9 REPLIES 9
Super User

Hi,

Assuming the date range is built from a Calendar Table, write these measures

Total = sum(Data[Sales])

Days selected = max(calendar[date])-min(calendar[date])+1

Total 1 = calculate([Total],datesbetween('calendar'[date],min(calendar[date])-[days selected],min(calendar[date])-1))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Perfect and works flawlessly. I was working on this for 2 days. Boy do I suck. You guys make it seem so easy.

Quick question. Is there way that I can show the output of the previous date ranges? Thanks. I will also add as a new topic.

Super User

You are welcome.  If you wish to show the date range, then write this measure

Measure1 = min(calendar[date])-[days selected]&" - "&min(calendar[date])-1

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Thanks for your help on this. It worked on my dev file but when I tried to copy and paste into my working file the measure won't work. I don't know what is going on. The syntax is the same only it is aggregating a different field. Can you see what might be going on. Below is the measure and it is showing blank

Thanks and let me know if you have issues accessing the file.

Super User

There is no Prevopenrate measure in that PBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Try it now.  Use the same link. You will see the Card visual on Page 1.

Thanks Ashish

Super User

Hi,

This measure works

``PrevOpenRate % 1 = calculate([Open Rate], PREVIOUSQUARTER('Date'[Date]))``

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

@helpysherriff Well, you could do something like this:

``````Measure =
VAR __MaxSelected = MAX( 'Dates'[Date] )
VAR __MinSelected = MIN( 'Dates'[Date] )
VAR __DaysSelected = ( __MaxSelected - __MinSelected ) * 1.
VAR __MaxDate = __MinSelected - 1
VAR __MinDate = __MaxDate - __DaysSelected
VAR __Table = FILTER( 'Table', [Date] >= __MinDate && [Date] <= __MaxDate )
VAR __Result = /* do something here with an X aggregator and __Table */
RETURN
__Result``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Thanks Greg.

I attempted to do it but come with a blank value.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors