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 ...
  • 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.

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.