cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jeremy_R_b Frequent Visitor
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
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
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.

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
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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors