Per day sum filtered by date and id

[monogamer]

Hello, all.

I have a balance table loaded into Power BI with the following shape:

This table is then connected to 2 other table, Calendar and Accounts, by the Date and Account_id column, respectively.

What I need to do is do is create 2 measures that calculated the maximum value found and its lastest corresponding date for:

   - All selected accounts (number of accounts can vary), while being able to set which accounts I want "active" via slicer.

   - The selected time frame, while being able to select said time frame via slicer.

So, having all accounts and dates available, the value should be 14 750 euro, on 2023/09/05, while if only having account 1 "active", the value should be 10 152 euro, on 2023/09/09.

You can download the demo file here:

https://drive.google.com/file/d/1eFwNcYaLRdQm9C-BC30BwLNdwGved2i3/view?usp=drive_link

[FreemanZ · Accepted solution]

hi @monogamer ,

not sure if i fully get you, try like:

Max Daily Balance = 
MAXX(
    ADDCOLUMNS(
        VALUES(CALENDAR[date]),
        "DailySum",
        CALCULATE(SUM(Balances[Balance]))
    ),
    [DailySum]
)

and 

Max Daily Balance Date = 
VAR _max = [Max Daily Balance]
VAR _date =
MAXX(
    FILTER(
        ADDCOLUMNS(
            VALUES(CALENDAR[date]),
            "DailySum",
            CALCULATE(SUM(Balances[Balance]))
        ),
        [DailySum]=_max
    ),
  CALENDAR[date]  
)
RETURN _date

it worked like:

[monogamer]

Thank you so much, @FreemanZ! From all the testing I did on the demo and the actual report, those 2 measures seem to hit the nail right on the head!

Also I guess I finally learnt how to make calculated columns dynamic by storing them in a variable. I either kept using MAX or calculating a new column but wasn't being able to connect the 2 processes together to make it dynamic.

[Rupak_bi]

will see the demo file, meanwhile try this

make a summarize table of balance group by date and department.

create measure of max balance = calculate(max(summarize [balance]), allexcept(summarize, summarize [department]).

create measure of max balance date = calculate(max(summarize [date]), allexcept(summarize, summarize [department]),balance = balance measure)