Measures that respond to a slicer/filter selection and still total.

[ryelesw]

Hello, I'm a beginner with PowerBI and was attempting something I think might not be possible with the tool. Hoping someone can provide some guidance on how best to proceed?

I have a table with accounts and balances across time for multiple accounts. I want to expose a slicer that allows users to specify a range of dates. Once a selection has been made, I want to return the change in balance per account from the start of the date range selected to the end of the date range selected. I want to do this for all accounts with records within the date range (per account). 

I then want to create a cross table/matrix to summarize how many accounts had a negative change in balance, positive change in balance and no change in balance.

So far I've managed to calculate the min and max dates as per the range for each account in the dataset, I'm also able to isolate the starting balance and ending balance for those accounts and calculate the difference. I'm stuck with how to present these in a matrix and summarize how many are positive, negative and no change.

I had to create a separate date table to use in the slicer the user can manipulate and establish a relationship between the date in that date table and my balance date field.

I've had to use measures to do this and these are the formulas that have worked for me.

Max Date in Range = Calculate ( MAX ( Balance_Date) , Account = SELECTEDVALUE ( Account ) && Balance_Date in ALLSELECTED ( DateTable[Date] ) )

Min Date in Range = Calculate ( MIN ( Balance_Date) , Account = SELECTEDVALUE ( Account ) && Balance_Date in ALLSELECTED ( DateTable[Date] ) )

Earliest Balance in Range = CALCULATE ( AVG (Balance) , Account = SELECTEDVALUE (Account) && Balance_Date = [Min Date in Range] )

Final Balance in Range = CALCULATE ( AVG (Balance), Account = SELECTEDVALUE (Account) && Balance_Date = [Max Date in Range] )

Change in Balance over Range  =  [Final Balance in Range] - [Earliest Balance in Range]

this allows me to create a table like the below screenshot (Recreated the table as i see it excel, highlighted the rows I expect to see as the first date - yellow and last date - green). In my example below, the user has selected the date range through the slicer to be 3/1/2024 - 6/1/2024. Notice there are records for some accounts that are outside this range, but the formulas ignore those and look for the earliest balance date and latest balance date WITHIN the selected range to get balance values.

I now want to be able to summarize the results as such. This summary must also be dynamic, in that it responds to changes the user makes to the filter selection.

Can this be done? And are there other alternatives to get to a similar-ish analysis?

Thanks!

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-jialongy-msft]

Hi @ryelesw 

You’ll need to create measures to categorize the changes as positive, negative, or no change. Here are the DAX formulas for these measures:

Positive Change = CALCULATE(COUNTROWS(AccountsTable), FILTER(AccountsTable, [Change in Balance over Range] > 0))

Negative Change = CALCULATE(COUNTROWS(AccountsTable), FILTER(AccountsTable, [Change in Balance over Range] < 0))

No Change = CALCULATE(COUNTROWS(AccountsTable), FILTER(AccountsTable, [Change in Balance over Range] = 0))

 You can create a summary table to display the counts of positive, negative, and no change accounts. Use the following DAX formula to create a new table:

SummaryTable = 
UNION(
    ROW("Change Type", "Positive", "Count", [Positive Change]),
    ROW("Change Type", "Negative", "Count", [Negative Change]),
    ROW("Change Type", "No Change", "Count", [No Change])
)

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.