cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!:
The Definitive Guide to Power Query (M)

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

Thanks Greg.

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.