Forum Discussion

vkisa's avatar
vkisa
Icon for Helper II rankHelper II
1 year ago
Solved

Diagonal Matrix and Cumulative Sum with Measures

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                  

 FebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovember
2024          
February574.83-245.53994.60797.18154.9399.881.53-32.83123.7311.00
March 947.89716.00715.47414.50404.45355.17268.33190.1263.31
April  1,087.84703.53492.30562.71250.93179.04201.2242.23
May   1,183.77900.36554.42476.24207.56316.3539.00
June    1,761.011,365.64600.22344.79181.83135.46
July     1,541.87946.28245.16548.3727.37
August      742.01460.82343.5473.95
September       568.05474.6558.73
October        860.62126.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                  

 FebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovember
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.11707.891,423.361,837.862,242.312,597.482,865.813,055.933,119.24
April  -311.16392.37884.671,447.381,698.311,877.352,078.582,120.81
May   -1,112.23-211.87342.55818.791,026.351,342.711,381.71
June    -1,163.99201.65801.871,146.651,328.481,463.94
July     -1,115.13-168.8476.31624.68652.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.62986.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

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

11 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      vkisa
      Icon for Helper II rankHelper II
      1 year ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        1 year ago

        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's avatar
    vkisa
    Icon for Helper II rankHelper II
    1 year ago

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

    Regards,

    Veli

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        vkisa
        Icon for Helper II rankHelper II
        1 year ago

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