Diagonal Matrix and Cumulative Sum with Measures

[vkisa]

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

[vkisa]

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

Regards,

Veli

[v-linhuizh-msft · Accepted solution]

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.

[vkisa]

Thank you @v-linhuizh-msft I appreciate your effort, it is worked. 

[lbendlin]

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.

[vkisa]

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.

[lbendlin]

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.

[vkisa]

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.  

[lbendlin]

What should the date column be for the Cost table?

[vkisa]

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

[v-linhuizh-msft]

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

[vkisa]

Hi @v-linhuizh-msft ,

I have refreshed the link.

Thank you for your message.