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.

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

        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.