topic Re: Per day sum filtered by date and id in DAX Commands and Tips

Per day sum filtered by date and id

monogamer — Wed, 22 Nov 2023 13:50:54 GMT

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

Re: Per day sum filtered by date and id

Rupak_bi — Thu, 23 Nov 2023 06:05:54 GMT

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)

Re: Per day sum filtered by date and id

FreemanZ — Thu, 23 Nov 2023 07:09:41 GMT

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:

Re: Per day sum filtered by date and id

monogamer — Thu, 23 Nov 2023 11:42:52 GMT

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.