Creating Matrix Visual with Cumulative Sum and Ratio Calculation

[sahgir123]

I have 2 table

Sales Date	Complaint Date	Datezdiff	Product	total value
Jan-24	Jan-24	0	A	2
Jan-24	Feb-24	1	A	3
Mar-23	Jun-23	3	A	3
Apr-23	Oct-23	6	A	4
May-23	Feb-24	9	A	5
Jun-23	Jun-24	12	A	6
Jan-23	Jan-23	0	A	7
Jan-23	Apr-23	3	A	8
Jan-24	Mar-24	2	A	2
Jan-24	Apr-24	3	A	1
Jan-24	May-24	4	A	2
Jan-24	Jun-24	5	A	5
Jan-24	Jul-24	6	A	2
Jan-24	Aug-24	7	A	2
Jan-24	Sep-24	8	A	4
Jan-24	Oct-24	9	A	1
Jul-23	Apr-24	9	A	3
Oct-23	Apr-24	6	A

and second table contain sales value monthly basis

Sale Date	Total Sale
Jan-24	23
Feb-24	34
Oct-23	46
Mar-24	23
Jul-23	50

and Output table i want like this 

Output
Complaint Date	Apr-24
Datediff		3	6	9
Product	A	8	16	27

 and

Complaint Date	Apr-24
Datediff		3	6	9
Product	A	0.347826	0.347826	0.54

I want a result in same way in matrix visual. in value i have used cumulative sum of total value.
for eg. if complaint date is april 24 and datediff is 3 then complaint date go back 3 month, so it will start with Complaint Jan 24 , similarly if complaint date is April 24 and datediff is 6 so it will go back 6 month so month will start with Oct 23 - April 24 and same logic for 9 datediff value.
and also want one more calculation which show ratio, for eg if cpmplaint date is April 24 and datediff is 3 then result should be  8/23 ---cumulative total value / total sales(from sale table) , second example if complaint date is April 24 and datediff is 6 then result will be 16/ 46 - (46 is value for oct23 sale date from second table) why 46 becuase complaint date is April 24 and datediff we are using 6 so it will go 6 month before, so sale month will be Oct23. similary for 9 so it will 27/50.

Request for Assistance:

I'm struggling to implement this logic in Power BI to create the desired matrix visual. Could someone please guide me on how to achieve this? Any suggestions, DAX calculations, or Power BI techniques would be greatly appreciated!

Thank you in advance for your help!

Best regards,

[Anonymous · Accepted solution]

Hi @sahgir123 ,

The Table data is shown below:

'Table'

'Table2'

Please follow these steps:
1. Use the following DAX expression to create columns in 'Table'

Sales_Date = 
VAR _a = RIGHT([Sales Date],3)
VAR _b = VALUE(LEFT([Sales Date],2))
VAR _c = SWITCH(TRUE(),
_a = "Jan",1,
_a = "Feb",2,
_a = "Mar",3,
_a = "Apr",4,
_a = "May",5,
_a = "Jun",6,
_a = "Jul",7,
_a = "Aug",8,
_a = "Sep",9,
_a = "Oct",10,
_a = "Nov",11,
_a = "Dec",12
)
RETURN  _b * 100 + _c

Complaint_Date = 
VAR _a = RIGHT([Complaint Date],3)
VAR _b = VALUE(LEFT([Complaint Date],2))
VAR _c = SWITCH(TRUE(),
_a = "Jan",1,
_a = "Feb",2,
_a = "Mar",3,
_a = "Apr",4,
_a = "May",5,
_a = "Jun",6,
_a = "Jul",7,
_a = "Aug",8,
_a = "Sep",9,
_a = "Oct",10,
_a = "Nov",11,
_a = "Dec",12
)
RETURN  _b * 100 + _c

2.Use the following DAX expression to create a table

Table3 = 
VAR _24_Apr = SELECTCOLUMNS(FILTER('Table','Table'[Complaint Date] = "24-Apr"),"Complaint Date",[Complaint Date],"Datezadiff",[Datezdiff])
VAR _24_Jan = SELECTCOLUMNS(FILTER('Table','Table'[Complaint Date] = "24-Jan"),"Complaint Date",[Complaint Date],"Datezadiff",[Datezdiff])
VAR _24_Feb = SELECTCOLUMNS(FILTER('Table','Table'[Complaint Date] = "24-Feb"),"Complaint Date",[Complaint Date],"Datezadiff",[Datezdiff])
RETURN UNION(_24_Jan,_24_Feb,_24_Apr)

3.Use the following DAX expression to create columns in 'Table3'

24-Jan = IF([Complaint Date] = "24-Jan",[Datezadiff]) 

24-Apr = IF([Complaint Date] = "24-Apr",[Datezadiff]) 

4.Use the following DAX expression to create  measures

Total = 
var _a = SWITCH(TRUE(),
ISFILTERED('Table3'[24-Apr]),"24-Apr",
ISFILTERED('Table3'[24-Jan]),"24-Jan")  
VAR _b = SELECTEDVALUE('Table3'[Datezadiff])
VAR _c = MAXX(FILTER('Table','Table'[Complaint Date] = _a),[Complaint_Date])
VAR _d = MAXX(FILTER('Table','Table'[Complaint Date] = _a && 'Table'[Datezdiff] = _b),[Sales_Date])
VAR _e = SUMX(FILTER('Table','Table'[Complaint_Date] <= _c && 'Table'[Sales_Date] >= _d ),[total value]) //It is used to calculate the total value, and the judgment condition can be modified according to your requirements.
VAR _f = IF(ISBLANK(SELECTEDVALUE('Table3'[Datezadiff])),BLANK(),_e)
RETURN _f

% = 
var _a = SWITCH(TRUE(),
ISFILTERED('Table3'[24-Apr]),"24-Apr",
ISFILTERED('Table3'[24-Jan]),"24-Jan")  
VAR _b = SELECTEDVALUE('Table3'[Datezadiff])
VAR _c = MAXX(FILTER('Table','Table'[Complaint Date] = _a),[Complaint_Date])
VAR _d = MAXX(FILTER('Table','Table'[Complaint Date] = _a && 'Table'[Datezdiff] = _b),[Sales_Date])
VAR _e = MAXX(FILTER('Table','Table'[Sales_Date] = _d),[Sales Date])
RETURN DIVIDE([Measure],LOOKUPVALUE(Table2[Total Sale],Table2[Sale Date],_e))

5.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.