Forum Discussion
vkisa
Helper II
Helper IIDiagonal 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 ...
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,
ZhuIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
vkisaHelper II
Helper IIThank you Anonymous I appreciate your effort, it is worked.