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

Finding the difference between two amounts within a slicer range

Hello all, I'm very new to DAX and fairly new to Power BI in general.  I have done a lot of searching and it may be I'm not phrasing my question correctly but I just can't seem to find the solution to this.

 

The following is the data from a KPI visual, it's a "Count" of something on that particular SnapShotDate.  (the visual just displays the total for the end of the range, which is 631 in this example)

 


DataListing.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This particular DateRange is selected by using a slicer for SnapShotDate.  

 

What I want to do is display another KPI visual that shows the difference between the "Count" column of the first selected date of the Slicer and the Last selected date of the Slicer.  So for this example, basically show [69] (which is 631-562).  It should change dynamically depending on the date selections.

 

I've searched through LOOKUPVALUE to see if I could use a min() or max(), and it's not a ROW by ROW calculation which I've seen a lot of tutorials on, I'm not really sure what the correct syntax or function should be used for this?

 

Any help or point in the correct direction would help, thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Finding the difference between two amounts within a slicer range

Hi,

 

Try this

 

=CALCULATE(SUM(your_measure),FILTER(Data,Data[snapshotdate]=MAX(calendar[date])))-

CALCULATE(SUM(your_measure),FILTER(Data,Data[snapshotdate]=MIN(calendar[date])))

 

Ensure the following:

 

1. There is a calendar table

2. There should be a relationship from the Snapshotdate in the Data table to the Date column in your calendar table

3. In the KPI visual, drag the date from the calendar table

4. The slicer should be built from the Date column of the calendar table

 

Hope this helps.


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

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

Re: Finding the difference between two amounts within a slicer range

Hi,

 

How about

 

=MAX(your_measure)-MIN(your_measure)


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

Re: Finding the difference between two amounts within a slicer range

Hello Ashish, thank you for the reply.

 

I think the issue is that the numbers represented in the "Count" aren't always greater than the previous.  So I could have a MIN that would be half way down the list but I would need to pull the related "Count" value associated with the first date. 

 

I may be misunderstanding your answer also.  I need the value of "Count" associated with the MIN date and MAX date (those are the two numbers I need to do calculation on).  Those dates are dependent on a slicer.  The numbers in the column are also a SUM, the above column is the "see data" when I look at the KPI visual.

Highlighted
Super User IV
Super User IV

Re: Finding the difference between two amounts within a slicer range

Hi,

 

Try this

 

=CALCULATE(SUM(your_measure),FILTER(Data,Data[snapshotdate]=MAX(calendar[date])))-

CALCULATE(SUM(your_measure),FILTER(Data,Data[snapshotdate]=MIN(calendar[date])))

 

Ensure the following:

 

1. There is a calendar table

2. There should be a relationship from the Snapshotdate in the Data table to the Date column in your calendar table

3. In the KPI visual, drag the date from the calendar table

4. The slicer should be built from the Date column of the calendar table

 

Hope this helps.


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

View solution in original post

Community Support
Community Support

Re: Finding the difference between two amounts within a slicer range

Hi @jeremy_R_b.

 

If you want to calculate based on slicer, you can also try to use firstdate and lastdate to combo with allselected function.

Diff = SUMX(FILTER(ALL(DateTable),[Date]=LASTDATE(ALLSELECTED(CALENDAR[Date]))),[Amount])- SUMX(FILTER(ALL(DateTable),[Date]=FIRSTDATE(ALLSELECTED(CALENDAR[Date]))),[Amount])

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
jeremy_R_b
Frequent Visitor

Re: Finding the difference between two amounts within a slicer range

Thanks Ashish, this seemed to work. I was having a comparison error but I realized I was using two different types of data for 'Data[snapshotdate]' and 'MAX(calendar[date])'.

 

Appreciate it!

 

@v-shex-msft I appreciate the additional help, I'll keep it in mind!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors