cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## DAX Wizards - SUM of balance on oldest date before a given date.

Hello great people of this community. Please hear my tale of what I'm trying to do:

1. I have a table looking like, below. Which for each day has an existing user id and their final balance of that day. A user is not necessarily active every day.
 Date User Balance 29/10/2017 1 10 29/10/2017 2 15 29/10/2017 3 5 30/10/2017 1 15 30/10/2017 3 10 31/10/2017 1 20 31/10/2017 2 5 31/10/2017 3 20

2. I want to build a table , that has for each day, the total of all the users ACTIVE on that day, and the total balance of the users NOT-ACTIVE. In this case it would be:

 Date Active_Total Non_Active_Total 29/10/2017 30 0 30/10/2017 25 15 31/10/2017 45 0

I can't seem to work it out for it to be relative to the date in the result table. If I use something like MAXDATE, then - in this case - on October 30th, for user 2 it would use balance = 5, where it should be 15 (on october 30th the balance for that user is the balance on the oldest available date <= of october 30th).

I've only recently started using PowerBI/DAX but I can't seem to produce the result I want. Any help or tips more than welcome 🙂

EDIT: thanks Ashish_Mathur for pointing out the typo in the non-active balance.
2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV

## Re: DAX Wizards - SUM of balance on oldest date before a given date.

Hi,

Here is the PBI desktop version.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User III

## Re: DAX Wizards - SUM of balance on oldest date before a given date.

Check this file

Use this MEASURE for Non Active Total

```Non Active Total =
VAR Currentday =
VALUES ( TableName[Date] )
RETURN
IF (
HASONEVALUE ( TableName[Date] ),
SUMX (
EXCEPT (
ALL ( TableName[User] ),
CALCULATETABLE ( VALUES ( TableName[User] ), TableName[Date] = Currentday )
),
VAR mydate =
CALCULATE (
LASTNONBLANK ( TableName[Date], 1 ),
FILTER ( ALL ( TableName[Date] ), TableName[Date] < Currentday )
)
RETURN
CALCULATE (
LASTNONBLANK ( TableName[Balance], 1 ),
FILTER ( ALL ( TableName[Date] ), TableName[Date] = mydate )
)
)
)```
Try my new Power BI game Cross the River
23 REPLIES 23
Super User III

## Re: DAX Wizards - SUM of balance on oldest date before a given date.

@stijn977

Try this Measure

```Non Active Total =
VAR Priorday =
PREVIOUSDAY ( VALUES ( Table1[Date] ) )
VAR Currentday =
VALUES ( Table1[Date] )
RETURN
IF (
HASONEVALUE ( Table1[Date] ),
CALCULATE (
SUM ( Table1[Balance] ),
EXCEPT (
ALL ( Table1[User] ),
CALCULATETABLE ( VALUES ( Table1[User] ), Table1[Date] = Currentday )
),
Table1[Date] = Priorday
)
)```
Try my new Power BI game Cross the River
Super User IV

## Re: DAX Wizards - SUM of balance on oldest date before a given date.

Hi,

Active_Total column in Table2 is a summation of the Balance column from Table1 - i get it till there.  How does one arrive at the numbers in the Non_Active_Total column?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support Team

## Re: DAX Wizards - SUM of balance on oldest date before a given date.

HI @stijn977,

You can try to use below formula to create new table show the summary data.

Table formula:

```Summary Table =
SUMMARIZE ( Test, [Date], "Active_Total", SUM ( Test[Balance] ) )```

Calculate column:

```Not_Active_Total =
VAR previous_Date =
MAXX (
FILTER ( 'Summary Table', [Date] < EARLIER ( 'Summary Table'[Date] ) ),
[Date]
)
VAR current_User_Count =
COUNTX (
FILTER ( ALL ( Test ), [Date] = EARLIER ( 'Summary Table'[Date] ) ),
[User]
)
VAR previous_User_Count =
COUNTX ( FILTER ( ALL ( Test ), [Date] = previous_Date ), [User] )
RETURN
IF (
current_User_Count < previous_User_Count,
SUMX ( FILTER ( ALL ( Test ), Test[Date] = previous_Date ), Test[Balance] )
- LOOKUPVALUE ( 'Summary Table'[Active_Total], 'Summary Table'[Date], [Date] ),
0
)
```

Result:

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Regular Visitor

## Re: DAX Wizards - SUM of balance on oldest date before a given date.

Hi, yes, that's exactly the question I am trying to answer 🙂

The logical reasoning would be:
- Find all the users that were NOT active on certain date
- Find the most recent entry for EACH user who was NOT active on this certain day, and add these all up. This could be the previous day or a week/month ago. So something along the lines of "get the balance for that user (who was not active today) , where the MAX(date) is < certain day and add up all the balances you've found.

==> This would give you on any given date, the total balance of all the users who were not active (and those who were active). Even if those users would come back at a later stage, because the search would be based on "MAX(date) is < certain day" , you can always find back what the total_balance of users who were not active on a certain date was (and see the evolution over time).

Hope that helped in explaining some 🙂

Regular Visitor

## Re: DAX Wizards - SUM of balance on oldest date before a given date.

Thanks for taking the time 🙂

there a couple of issues with your logic I'm afraid.

- Using "previous date" does not work here, because some users might have had their balance updated (which means that day they were active) a week or a month ago. So going back just one day is not sufficient , it should be something a long the lines of the MAX(test[date]) < SummeryTable[Date] for each user not active today. Unless I am wrong on the meaning of earlier of course, but testing it it seems to calculate "given date - 1".

- current_User_Count < previous_User_Count assumes there is a set and limited number of users in the database. It is well possible that new users join (meaning, they are active) on a given day , so the current_User_Count would be larger than the previous_User_Count, even though the previous day a number of users was not active.

But thank you for taking the time. If anything evaluating the solution helps me understand the DAX functions.

Regular Visitor

## Re: DAX Wizards - SUM of balance on oldest date before a given date.

Hello, thank you for your suggestion. However we can not be sure that we only need to go back one day to find the balance of the users not active today. This could be the previous day, or a week or month ago. So it need to be something along the lines of "get the balance for that users (who are not active today) , where the MAX(date) is < the day you're calculating for,  and add up all the balances you've found for all these users that were not active.

But thank you for taking the time, I did not know about the EXCEPT functionality, so I might try and experiment along those line! thanks

Super User IV

## Re: DAX Wizards - SUM of balance on oldest date before a given date.

Hi @stijn977,

On 30/10/2017, only user 2 was inactive.  The balance against 2 on a previous day was 15.  Shouldn't the answer be 15?  Why do you have 5 there?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

## Re: DAX Wizards - SUM of balance on oldest date before a given date.

you're right that's a typo 🙂 sorry for making this even more confusing than it already is! I will edit the original post.

Super User IV

## Re: DAX Wizards - SUM of balance on oldest date before a given date.

Hi @stijn977,

You may refer to my solution this workbook.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors