Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello,
I have the following model;
I have a measure that calculates the number of reactivated users for each calendar date;
Reactivated Users = VAR _MaxDate = MAX ( 'D Calendar'[Date] ) VAR _CurrentUsers = CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Order'[IsCompleted] = "Yes" ) VAR _UserBefore60Days = CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Calendar'[Date] < _MaxDate - 60, 'D Order'[IsCompleted] = "Yes" ) VAR _UsersLast60Days = CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Calendar'[Date] >= _MaxDate - 60 && 'D Calendar'[Date] <= _MaxDate - 1, 'D Order'[IsCompleted] = "Yes" ) VAR _Result = CALCULATE ( DISTINCTCOUNT ( 'F Orders'[UserId] ), FILTER ( ALL ( 'F Orders'[UserId] ), 'F Orders'[UserId] IN _CurrentUsers && 'F Orders'[UserId] IN _UserBefore60Days && NOT 'F Orders'[UserId] IN _UsersLast60Days ) ) RETURN _Result
I would like to display the total sum of all dates as the total for the month;
Does anyone have an idea how to achieve the desired result?
Many thanks in advance!
Solved! Go to Solution.
[Reactivated Users - Helper Measure] = -- hidden measure // This measure works correctly on an individual // day level. VAR _MaxDate = MAX ( 'D Calendar'[Date] ) VAR _CurrentUsers = CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Order'[IsCompleted] = "Yes" ) VAR _UsersBefore60Days = CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Calendar'[Date] < _MaxDate - 60, 'D Order'[IsCompleted] = "Yes" ) VAR _UsersLast60Days = CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Calendar'[Date] >= _MaxDate - 60, 'D Calendar'[Date] <= _MaxDate - 1, 'D Order'[IsCompleted] = "Yes" ) VAR _UsersOfInterest = except( intersect( _CurrentUsers, _UsersBefore60Days ), _UsersLast60Days ) VAR _Result = countrows ( _UsersOfInterest ) RETURN _Result [Reactivated Users] = var __reactivatedUserCount = sumx( 'D Calendar', [Reactivated Users - Helper Measure] ) return __reactivatedUserCount
[Reactivated Users - Helper Measure] = -- hidden measure // This measure works correctly on an individual // day level. VAR _MaxDate = MAX ( 'D Calendar'[Date] ) VAR _CurrentUsers = CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Order'[IsCompleted] = "Yes" ) VAR _UsersBefore60Days = CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Calendar'[Date] < _MaxDate - 60, 'D Order'[IsCompleted] = "Yes" ) VAR _UsersLast60Days = CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Calendar'[Date] >= _MaxDate - 60, 'D Calendar'[Date] <= _MaxDate - 1, 'D Order'[IsCompleted] = "Yes" ) VAR _UsersOfInterest = except( intersect( _CurrentUsers, _UsersBefore60Days ), _UsersLast60Days ) VAR _Result = countrows ( _UsersOfInterest ) RETURN _Result [Reactivated Users] = var __reactivatedUserCount = sumx( 'D Calendar', [Reactivated Users - Helper Measure] ) return __reactivatedUserCount
Dear Power BI team, give this man @Anonymous a statue for his contribution to this forum!
@Anonymous why is the daily number of reactivated users different from my first measure with a monthly total of 2226?
@Greg_Deckler thanks for your reply and interesting posts!
The total sum of the daily numbers is correct. It is matching with a manual addition of all days so thanks a lot for that.
My doubts are based on the fact that your measure is producing a different daily result than my old measure, and I'm not sure which change you made in the new measure is creating this difference. Anyway, thanks again!
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
9 | |
9 | |
6 | |
5 |
User | Count |
---|---|
17 | |
15 | |
10 | |
9 | |
8 |