Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Rolling calculation formula help

Hi,   I have a matrix that shows budget to actuals than the percentage of that spent. The % spent is measure that is supposed to be a rolling number on to the next month, like if one month is 10% a...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    v-jiascu-msft

     

    I tried the code you gave and it seems to still give me issues. I think it is messing up with the different department budgets, but unsure.

     

    For this example i have two department payrolls in separate matrix, then filtered to only be that certain department. Here is what they look like. I have also added a OneDrive link to the two tables that are used for these matrix. 

     

    Tables in Excel file

     

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous,

 

It seems you should try dax/summarize-function-dax in your scenario. It could be like below. If you'd like a precise formula, please provide a sample. Please mask the sensitive parts.

 

% Spent Exp =
CALCULATE (
    SUMX (
        SUMMARIZE (
            GlEntry,
            [Account],
            [Month],
            "%Spent", DIVIDE ( SUM ( GlEntry[Expense] ), MAX ( GlEntry[Expense Budget] ) )
        ),
        [%Spent]
    ),
    FILTER (
        ALLEXCEPT ( GLEntry, Glacount[Account] ),
        GLEntry[Entry_date] <= MAX ( GLEntry[Entry_date] )
    )
)

 

Best Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    v-jiascu-msft

     

    I tried the code you gave and it seems to still give me issues. I think it is messing up with the different department budgets, but unsure.

     

    For this example i have two department payrolls in separate matrix, then filtered to only be that certain department. Here is what they look like. I have also added a OneDrive link to the two tables that are used for these matrix. 

     

    Tables in Excel file

     

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      7 years ago

      Hi Anonymous,

       

      Please make sure your sample doesn't contain anything sensitive.

      There are two solutions in the attached demo. Please download it. If you'd like the [solution 2] that needs no new table, please delete the relationship between [Calendar] and [Glentry].

      % Spent Exp 2 =
CALCULATE (
    SUMX (
        SUMMARIZE (
            GlEntry,
            Glaccount[Account],
            'Calendar'[Date].[Year],
            'Calendar'[Date].[Month],
            "%Spent", DIVIDE ( SUM ( GlEntry[Expense] ), MAX ( GlEntry[Expense Budget] ) )
        ),
        [%Spent]
    ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
)

      Rolling-calculation-formula-help

       

      Best Regards,