Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a measure that calculates reactivated users by date, and this produces the expected result.
However, I am not able to produce the sum of reactivated users for all days within the selected month.
When I manually add up all reactivated users for a month, it does not match with the result produced on monthly level by the measure.
Does anyone have an idea why the monthly total is producing a different result from adding the daily numbers up manually?
Regards,
Adrian
Reactivated = VAR __CurrentUsers = CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Order'[IsCompleted] = "Yes" ) VAR __UsersLast180Days = CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), FILTER ( ALL ( 'D Calendar'[Date] ), 'D Calendar'[Date] >= MIN ( 'D Calendar'[Date] ) - 180 && 'D Calendar'[Date] <= MIN ( 'D Calendar'[Date] ) - 1 ), 'D Order'[IsCompleted] = "Yes" ) VAR __UserBefore180Days = CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), FILTER ( ALL ( 'D Calendar'[Date] ), 'D Calendar'[Date] < MIN ( 'D Calendar'[Date] ) - 180 ), '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 __UserBefore180Days && NOT 'F Orders'[UserId] IN __UsersLast180Days ) ) RETURN IF ( ISINSCOPE ( 'D Calendar'[Date] ), __Result, SUMX ( VALUES ( 'D Calendar'[Date] ), CALCULATE ( CALCULATE ( DISTINCTCOUNT ( 'F Orders'[UserId] ), FILTER ( ALL ( 'F Orders'[UserId] ), 'F Orders'[UserId] IN CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Order'[IsCompleted] = "Yes" ) && 'F Orders'[UserId] IN CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), FILTER ( ALL ( 'D Calendar'[Date] ), 'D Calendar'[Date] < MIN ( 'D Calendar'[Date] ) - 180 ), 'D Order'[IsCompleted] = "Yes" ) && NOT 'F Orders'[UserId] IN CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), FILTER ( ALL ( 'D Calendar'[Date] ), 'D Calendar'[Date] >= MIN ( 'D Calendar'[Date] ) - 180 && 'D Calendar'[Date] <= MIN ( 'D Calendar'[Date] ) - 1 ), 'D Order'[IsCompleted] = "Yes" ) ) ) ) ) )
Hi Darek,
I've created the model and a report page to explain the issue; https://we.tl/t-3M2uO84dhh
The measure "Reactivated Users" is calculating the number of unique users that places an order on a selected date, but not placed an order in the last 40 days, but did place an order before 40 days ago. Therefor, 3 sets are prepared in variables to find if a user is in two of those sets and not in the set of orders in the last 40 days.
Basically I want the month total in the table to be equal to the manual sum of all days within the month. Somehow the measure is giving a result that is not equal to the total of all days.
Thanks in advance for your help!
Regards,
Adrian
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
32 | |
30 | |
18 | |
18 |