Forum Discussion

TH_BI's avatar
TH_BI
Frequent Visitor
4 years ago

Calculate Variance Dynamically Across 2 Matrices

I feel like the solution should be very simple but I'm stumped. Any help please?   I have two matrices (A and B). Each matrix has its own date filter/selection. Through interactions, I've managed t...
Need Help
amitchandak's avatar
amitchandak
Super User
4 years ago

TH_BI , You can try a measure below for variance

 

new measure =
var _max1 = maxx(allselected(Date),Date[Date])
var _min1 = minx(allselected(Date),Date[Date])
var _max2 = eomonth(_max1,2)

var _min = eomonth(_max1,-1* if( mod(Month(_max1),3) =0,3,mod(Month(_max1),3)))+1
var _max= eomonth(_min, 2)
return
if('Date'[Date]>= _min && 'Date'[Date] <= _max , "Latest Qtr" format([Date], "YYYY-QQ") )

return
calculate( sum(Table[Value]), filter('Table', 'Table'[Date] >=_min && 'Table'[Date] <=_max)) - calculate( sum(Table[Value]), filter('Table', 'Table'[Date] >=_min1 && 'Table'[Date] <=_max2))

 

  • TH_BI's avatar
    TH_BI
    Frequent Visitor
    4 years ago

    Thanks Amit! I'll give it a try.