cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
stijn977 Regular Visitor
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. 
    DateUserBalance
    29/10/2017110
    29/10/2017215
    29/10/201735
    30/10/2017115
    30/10/2017310
    31/10/2017120
    31/10/201725
    31/10/2017320


    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: 

    DateActive_TotalNon_Active_Total
    29/10/2017300
    30/10/20172515
    31/10/2017450
     
    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
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Super User III
Super User III

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

Hi @stijn977

 

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

View solution in original post

23 REPLIES 23
Super User III
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
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
https://www.linkedin.com/in/excelenthusiasts/
Community Support Team
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:

6.PNG

 

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
stijn977 Regular Visitor
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 🙂  


stijn977 Regular Visitor
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. 

stijn977 Regular Visitor
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
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
https://www.linkedin.com/in/excelenthusiasts/
stijn977 Regular Visitor
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
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
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

January 2020 Community Highlights

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

Top Solution Authors