Calculate difference between two filtered measures

[MrKrukauskas]

Hi Power BI Community, hope you all staying safe!

My first question here, so please don't judge me 😄

I have been trying to calculate revenue difference, for the same Division, between two different dates (Data Load Date and Historical Date).

Here the the User steps:

1. Selects Division
2. Selects Data Load  Date --->> 1st measure shows the revenue for that division for that date
3. Selects Historical Date --->> 2nd  measure shows the revenue for that division for that date
4. 3rd measure calculates the difference between the 1st and the 2nd measure. 

I have tried to use the same Data Load Date to create all 3 measures.

I also tried to duplicate the table and just renamed the second table and Data Load Date to Historical Date.

Table A:

Division

Revenue

Target

Data Load Date

Table B (a copy):

Division

Revenue

Target

Historical Date (rename from Data Load Date)

How can I create a measure to calculate the difference between 1st and 2nd value?

Much appreciate the help!

[jdbuchanan71]

@mase_53 

Campaign Type would be a completely sepereate filter that would have nothing to do with the dates.  You would eaither want the Campaign Type as a column in your main data table or have a Campaign table that has the field Campaign Type in it an that links to your main data table.

[amitchandak]

@MrKrukauskas , depending on how you want to choose the date you need on one or two date table.

Like if you need the same date range then you have join with one date table. One active and one inactive join. And use relation to activate join.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

In case you need a different date, You need to two date dimension. And in your formula, you need to all for the other date. Or you can use one of the date tables as disconnected and try .

Refer to this example. this done for one date. But you have to do for two dates

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[jdbuchanan71 · Accepted solution]

@MrKrukauskas 

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. 

[moysesmoreira]

I try to do the same thing, but I cant. Could you help me? 
My pbix is here: 
https://drive.google.com/file/d/1c5DpEI4rsoPX8U8EBepHJ9gSsSmckLmg/view 

[mase_53]

@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?

[MrKrukauskas]

@jdbuchanan71 Thanks! Worked like a charm. Now where is that Kudos button! 😄