Compare measure based on filter values

#1

Hi,
I have a measure for average discount and a slicer for the order date.
The order date is then being used as a drop down to allow a range of dates to be searched/filtered.

I would like to be able to add a new measure that returns the discount difference between 2 dates.

So say the average discount is 3.00 on the 12th and 1.00 on the 13th. If I change the filter to search for these 2 dates, I want a new column to show the discount difference of 1.
Of course, any 2 dates could be entered in the filters.
So if you searched for 12th May to 12th June, the colum would show the difference between the whole range.
comparison_example.xlsx (8.4 KB)

I have looked into a few things but not quite found a suitable solution.

Can anybody please point me in the right direction?

Thanks :slight_smile:

#2

Hi,

One possible way is to use two separate tables (same structure, same data) for separate dates with order_date in their respective slicers. You can use two single hierarchy calendar filters to connect to the two tables, so users can filter table_1 based on a selected “from” date and table_2 based on “to” date. Next a formula visualization can be used to subtract table_2 measure from table_1 measure or any other operation - something like in the following screenshot.

The two tables can always be hidden or kept out of the dashboard canvas. Make sure the filters are connected to individual tables as well as parameters (dates from parent visualizations) in the formula visualization.
image

1 Like