Finding the difference between two amounts within a slicer range

[jeremy_R_b]

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)

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!

[v-shex-msft]

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

[Ashish_Mathur]

Hi,

How about

=MAX(your_measure)-MIN(your_measure)

[jeremy_R_b]

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.

[Ashish_Mathur · Accepted solution]

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.

[jeremy_R_b]

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!