Calculate measures over last 3 month data

[Anonymous]

I have 2 date (date1 & date2) columns in my dataset and I'm trying to do calculate a measure(Weighted avg: WAC) for the last 3 months of date2 from selected date of date1

For eg:

When the selected date1 is: 9/30/2020, the measure is calculated for last 3 month of date2 column (as highlighted below):

I already have the DAX calculation for the entire table, I just couldn't figure out how to modify it to fit on the above data.

WAC = SUMX(Sheet1, Sheet1[Balance] * Sheet1[Rate])/SUM(Sheet1[Balance])

What modifications do I need to make to my DAX to get the desired output?

I've uploaded the pbix file here: https://drive.google.com/file/d/1TSs-uXMJkP-WN2C21l4ygsbF7fc8N_te/view?usp=sharing

[wdx223_Daniel]

[Anonymous]

@wdx223_Daniel I removed the ALL() filter from the DAX calculation you gave me and I'm able to get the correct numbers. The only issue is that I'm not getting the rows corresponding to categories where there is no data.

So for eg, if there is not data for Category C, I should get a blank row. I assume this is because of the default behaviour of sum product function, is there any way to modify the calculation and get the required results? I was thinking of doing a UNION of distinct categories with the current table, but not sure how.

[Anonymous]

Thanks for replying @wdx223_Daniel . The numbers seems to be a bit off. I want the sum product only for the last 3 months under Date2.

For eg: for date1 = 9/30/2020, the sumproduct of highlighted values should be 0.4635

I also noticed that you cleared the filter by using ALL function to clear all filters before sumproduct calculation.