topic Re: Diagonal Matrix and Cumulative Sum with Measures in DAX Commands and Tips

Diagonal Matrix and Cumulative Sum with Measures

vkisa — Mon, 18 Nov 2024 16:33:38 GMT

Hi,

I have two date tables and a main table with total profit and players that are first buyers. Also, there is a monthly costs in another table.

I want to calculate Retern of Profit in a matrix that is calculated as below tables.

Desired Table 1

Should be calculated as ProfitbyFTB=Profit/Count of First Time Buyer (diagonal)

For example: February 2024 Row, 22,993/40= 574.83 -9,821/40=-245.53...

March 2024 Row, 2,728,030/2878= 947.89 2,060,658/2,878=716.....

2024

	February	March	April	May	June	July	August	September	October	November
2024
February	574.83	-245.53	994.60	797.18	154.93	99.88	1.53	-32.83	123.73	11.00
March		947.89	716.00	715.47	414.50	404.45	355.17	268.33	190.12	63.31
April			1,087.84	703.53	492.30	562.71	250.93	179.04	201.22	42.23
May				1,183.77	900.36	554.42	476.24	207.56	316.35	39.00
June					1,761.01	1,365.64	600.22	344.79	181.83	135.46
July						1,541.87	946.28	245.16	548.37	27.37
August							742.01	460.82	343.54	73.95
September								568.05	474.65	58.73
October									860.62	126.12
November										320.58

Desired Table2

This table should be calcalulated as for first month of matrix: ProfitbyFTB-Cost then cumulative sum of ProfitbyFTB

For example: February 2024 Row: 574.83-7,189= -6,614.18 | -6,614.18+(-245.63)=-6,859.7 | -6,859.7+ 994.6 | .........

March 2024 Row: 947.89- 956=-8.11 | -8.11+ 716=707.89 | .....

2024

	February	March	April	May	June	July	August	September	October	November
2024
February	-6,614.18	-6,859.70	-5,865.10	-5,067.93	-4,913.00	-4,813.13	-4,811.60	-4,844.43	-4,720.70	-4,709.70
March		-8.11	707.89	1,423.36	1,837.86	2,242.31	2,597.48	2,865.81	3,055.93	3,119.24
April			-311.16	392.37	884.67	1,447.38	1,698.31	1,877.35	2,078.58	2,120.81
May				-1,112.23	-211.87	342.55	818.79	1,026.35	1,342.71	1,381.71
June					-1,163.99	201.65	801.87	1,146.65	1,328.48	1,463.94
July						-1,115.13	-168.84	76.31	624.68	652.05
August							-2,574.99	-2,114.17	-1,770.63	-1,696.68
September								-3,816.95	-3,342.30	-3,283.56
October									860.62	986.74
November										320.58

I created two measures for ProfitbyFTB and Count of First Time Buyer (diagonal) but then could not calculate cumulative summed measure.

You can find sample report by below link.

SampleReport

Thank you,

Veli

Re: Diagonal Matrix and Cumulative Sum with Measures

lbendlin — Fri, 08 Nov 2024 15:16:37 GMT

For a running total you would modify your filters to include the "selected" month and all prior months. To do that efficiently you could use the Months reference table (month number) or you could use proper disconnected calendar tables for your Date1 and Date2 dimensions. That would make things a lot simpler.

Re: Diagonal Matrix and Cumulative Sum with Measures

vkisa — Fri, 08 Nov 2024 15:22:02 GMT

Hi lbendlin , I have already try the independence month table and it partly works for getting desired table1 but still can not reach out desred table 2 and that is the main aim for my task. If you can look at the sample report, you can see what I mentioned.

Thank you for your reply.

Re: Diagonal Matrix and Cumulative Sum with Measures

lbendlin — Fri, 08 Nov 2024 17:24:21 GMT

Your Data and CostData tables should contain actual date columns rather than these exotic foreign keys. If your data has monthly granularity use the first day of the month for the date value.

Re: Diagonal Matrix and Cumulative Sum with Measures

vkisa — Fri, 08 Nov 2024 23:28:09 GMT

I have added date columns in both tables and updated the sample report, but still don't know how to get running totals by subtract on first value.

That would be great if you look at the report when you had time.

Re: Diagonal Matrix and Cumulative Sum with Measures

lbendlin — Sat, 09 Nov 2024 14:04:21 GMT

What should the date column be for the Cost table?

Re: Diagonal Matrix and Cumulative Sum with Measures

vkisa — Sat, 09 Nov 2024 19:46:52 GMT

I have updated the report as exact dates are added for date1FK,date2FK and cost table. It should be as below

Re: Diagonal Matrix and Cumulative Sum with Measures

Anonymous — Fri, 15 Nov 2024 01:57:35 GMT

Thanks for the replies from lbendlin.

Hi vkisa ,

I am unable to open the link you gave, could you provide a new link that is accessible, thanks in advance for your efforts!

Best Regards,
Zhu

Re: Diagonal Matrix and Cumulative Sum with Measures

vkisa — Fri, 15 Nov 2024 07:25:29 GMT

Hi Anonymous ,

I have refreshed the link.

Thank you for your message.

Re: Diagonal Matrix and Cumulative Sum with Measures

vkisa — Mon, 18 Nov 2024 16:36:19 GMT

The link is refreshed by drive link, I hope someone can help me to solve it.

Regards,

Veli

Re: Diagonal Matrix and Cumulative Sum with Measures

Anonymous — Fri, 22 Nov 2024 06:17:08 GMT

Hi vkisa ,

I modified two measures you provided and created a new one:

New_Profit = var year1=SELECTEDVALUE(Date1[Year]) var year2=SELECTEDVALUE(Date2[Year]) VAR selectedmonth=MAX(Months[MonthNum]) var date1=SELECTEDVALUE(Months[MonthNum]) var date2=SELECTEDVALUE(Date2[MonthNumber]) RETURN CALCULATE( SUM(Data[Profit]), FILTER(Data, Data[Year1]=2024 && Data[Month1]<=date1) )

New_Profit_agg = VAR selectedmonth=MAX(Months[MonthNum]) RETURN DIVIDE([New_Profit],[FTB_Count],0)

Measure = IF(MAX('Date2'[MonthNumber])<=MAX('Months'[MonthNum]),[New_Profit_agg]-CALCULATE(SUM('CostData'[Cost])))

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Diagonal Matrix and Cumulative Sum with Measures

vkisa — Wed, 27 Nov 2024 08:01:19 GMT

Thank you Anonymous I appreciate your effort, it is worked.