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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
helpysherriff
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.

helpysherriff_0-1685202197262.png

 



https://app.powerbi.com/groups/me/reports/b4316d14-63e9-454a-a3f9-5416a4767d05/ReportSection3f69c0f0... 

 


helpysherriff_0-1685200787871.png

 

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

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.

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
https://www.linkedin.com/in/excelenthusiasts/

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.

helpysherriff_0-1685317503136.png



helpysherriff_1-1685317511640.png


https://1drv.ms/u/s!AlTfdvX0WzaQ22s1rI3nvo1RxCdm?e=sKyhiM

 

There is no Prevopenrate measure in that PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Thanks Ashish


Hi,

This measure works

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks Greg.

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



helpysherriff_1-1685207961322.png



helpysherriff_2-1685207989408.png

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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