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

[stijn977]

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. 

[Ashish_Mathur · Accepted solution]

Hi,

Here is the PBI desktop version.

Hope this helps.

[stijn977]

Amazing, I can’t wait to try this! Will definitely let you know the result! 

[Zubair_Muhammad]

@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
        )
    )

[stijn977]

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