Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stijn977
Helper I
Helper I

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

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

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

Regards
Zubair

Please try my custom visuals

View solution in original post

23 REPLIES 23
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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. 

Ashish_Mathur
Super User
Super User

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/

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 🙂  


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/

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


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/

Hi! 🙂 

I’m on my iPad at the moment So the workbook opens in Excel, but I assume I will be able to find the DAX formulas in there somewhere? Just looking at the end result it looks like it works.

 

Hopefully I can use and port it to PowerBI. Excited to give it a go later, as I have been stuck on this.

 

Very much appreciated you taking the time though! Have a great weekend.

Hi,

 

Here is the PBI desktop version.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Kind sir @Ashish_Mathur you have done it! 🙂 ... I marked your reply as a solution, if there's anything else I can do to upvote you let me know! 


I already had a seperate table the dates and the relationship you used, so it ported over nicely to my workbook. 

I changed the hard coded date to be  " MIN('Calendar'[Date]) ". 

 

I'm going to make sure I actually understand your solution now. I guess the 'Date of previous interaction' seems to be crucial though it looks like  its only function is to get the previous day. 

Then the total_inactive measure:
- SUMMARIZE(all(Data[User]),[User],"ABCD",CALCULATE([Total_Active],DATESBETWEEN('Calendar'[Date],[Date of previous interaction],[Date of previous interaction])),"EFGH",[Total_Active]) 
This SUMARIZE is amazing, could you explain it in words? I'm sure that would help me and a lot of others. 
- The SUMMARIZED table, gets filtered on WHERE [EFGH]=0 , basically every user who did not have a total_active value. 
- Then SUMX add these all up. 

There is no need / use for 'sum of User' measure right? This would only sum all the user-id's which would be a useless value? I don't see it referenced anywhere else either. 

 

This is a very ellegant solution. Thank you. 

 

You are welcome.  Thank you for your kind words.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi all , I'm afraid I have to re-open or at least update this problem/solution. 

The formula works fine if all users are known on day 1. If new users join at a later date, then for all the days they did not even exist yet they get counted as being "inactive". 

NewCapture.PNG

 

Here's the formula:


Total_Inactive = IFERROR(SUMX(FILTER(SUMMARIZE(all(Data[User]),[User],"ABCD",CALCULATE([Total_Active],DATESBETWEEN('Calendar'[Date],[Date of previous interaction],[Date of previous interaction])),"EFGH",[Total_Active]),[EFGH]=0),[ABCD]),BLANK())

 

I have a table where I have the "join date" of a user. So for me the ' all(Data[User]) ' part from the formula, should only be for those set of users where the "join date" is before or on (<=) the date we're processing for. I have a relationship between the Data and "Users" table, but I can't add a filter on the ' all(Data[User]) ' part.


An alternative would be to only summarize on the id's that are in the Data table before or on this date, so not after (which is what the ALL does). This way we wouldn't have to find the ids in another table and check the "join date". Not sure which would be the best solution.

 

Anyone have any ideas how I could SUMMARIZE based on only those id's that qualify, and not all?

 

I'll keep looking!

Thanks 🙂

 

 

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

Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad this seems to work amazingly well! I managed to rebuild the solution and implement it in the final report, now i will have to manually do some double checks but for now I've marked it as the correct solution! 🙂 

The previous solution given was also correct, based on the requirements I had given. But your solution definitely is more complete and takes intoaccount new users! Amazing 🙂 Thank you for your help! 

Hi thanks so much for your contribution! I didn't yet have time to look at it but will definitely check it out tomorrow or over the weekend. Much appreciated! 

@stijn977

 

Also you can get the names of Non Active Customers on each date using this MEASURE

 

Non Active Customers =
VAR Currentday =
    VALUES ( TableName[Date] )
RETURN
    IF (
        HASONEVALUE ( TableName[Date] ),
        CONCATENATEX (
            EXCEPT (
                CALCULATETABLE ( VALUES ( TableName[User] ), TableName[Date] <= Currentday ),
                CALCULATETABLE ( VALUES ( TableName[User] ), TableName[Date] = Currentday )
            ),
            TableName[User],
            ","
        )
    )

Regards
Zubair

Please try my custom visuals

Thanks @Zubair_Muhammad

Getting a list of inactive id's is very useful. In my case there might be 1000s of inactive users / day ... so i wonder if it is possible to convert this into a count of inactive users instead of a (comma seperated) list? 

HI @stijn977

 

My apologies.I absolutely missed your question.

 

This would hopefully work

 

Count of Non Active Customers =
VAR Currentday =
    VALUES ( TableName[Date] )
RETURN
    IF (
        HASONEVALUE ( TableName[Date] ),
        COUNTROWS (
            EXCEPT (
                CALCULATETABLE ( VALUES ( TableName[User] ), TableName[Date] <= Currentday ),
                CALCULATETABLE ( VALUES ( TableName[User] ), TableName[Date] = Currentday )
            )
        )
    )

Regards
Zubair

Please try my custom visuals

No problem, you've been an amazing help. I will test this out as soon as I can. Thanks again! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.