Forum Discussion
Calculate difference between two filtered measures
I think what you will need is 2 date lists, one linked to each field [Data Load Date] and [Historical Date]
You use the dates from those 2 tables in the slicers and write the measures so the [Data Load Amonut] ignores the filters from the 'Historical Dates' table and vice versa.
Total Amount = SUM ('Table'[Amount] )Data Load Amonut = CALCULATE( [Total Amount],ALL('Historical Dates') )Historical Amount = CALCULATE( [Total Amount],ALL('Data Load Dates') )Difference = [Data Load Amonut] - [Historical Amount]On both of the date slicers I also added a filter where [Total Amount] is not blank so that only dates with an amount would show in the list.
I have attached my sample file for you to look at so you can see how I made the two date tables.
I think what you will need is 2 date lists, one linked to each field [Data Load Date] and [Historical Date]
You use the dates from those 2 tables in the slicers and write the measures so the [Data Load Amonut] ignores the filters from the 'Historical Dates' table and vice versa.
Total Amount = SUM ('Table'[Amount] )Data Load Amonut = CALCULATE( [Total Amount],ALL('Historical Dates') )Historical Amount = CALCULATE( [Total Amount],ALL('Data Load Dates') )Difference = [Data Load Amonut] - [Historical Amount]
On both of the date slicers I also added a filter where [Total Amount] is not blank so that only dates with an amount would show in the list.
I have attached my sample file for you to look at so you can see how I made the two date tables.
jdbuchanan71 I really hope you see this! Thank you so much for this explanation:
One further question: if I wanted to extend this functionality, so that I can have more than one filter - what is the best way to accomplish that?
For example: I want to filter by Historical Date, Amount Date, and Campaign Type. Do I need to also create two separate campaign type tables? Or is there a way to add campaign type information to the historical dates and data load dates tables?