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.
Hi all,
Sorry if this question has been answered before. But I struggle to find any answers.
My issue is, that I have a table showing me for each month how many signed up as well as how dropped out (in Power BI).
Dummy data:
And what I would like is a table that gave an overview month-to-month how many was signed up when the month started and how did the total change at the end of the month.
What I'm really struggling to figure out is how to get Power BI to evaluate this in the right sequence.
I hope someone can help me!
Thanks,
Hans
Solved! Go to Solution.
I was working on this at same time must be, and didn't see last reply. In any case, here is another way that results in this table (I only put 3 mos of example data in).
Here are the measures: The opening value is just the sum of all the prev sign ups minus sum of prev dropouts.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I was working on this at same time must be, and didn't see last reply. In any case, here is another way that results in this table (I only put 3 mos of example data in).
Here are the measures: The opening value is just the sum of all the prev sign ups minus sum of prev dropouts.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@hans_j_h , last month dropped out is your this month opening
last MTD Sales = CALCULATE([dropped out]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
@hans_j_hYou will need to create four measures.
1) One for start of month running total.
2) One for end of month running total.
3) One for increases.
4) one for decreases.
Hi @Anonymous
Thanks for your quick answer!
Could you elaborate? What DAX codes should I use?
Well its ugly but it works...Make measures 3 & 4 first.
1) One for start of month running total =
MonthStartTotal =
CALCULATE (
[Signed In],
FILTER (
ALLSELECTED ( 'Table'[MonthYear] ),
ISONORAFTER ( 'Table'[MonthYear] + 1, MAX ( 'Table'[MonthYear] ), DESC )
)
)
+ CALCULATE (
[Dropped Out],
FILTER (
ALLSELECTED ( 'Table'[MonthYear] ),
ISONORAFTER ( 'Table'[MonthYear] + 1, MAX ( 'Table'[MonthYear] ), DESC )
)
) + 0
2) One for end of month running total
MonthEndTotal =
CALCULATE (
[Signed In],
FILTER (
ALLSELECTED ( 'Table'[MonthYear] ),
ISONORAFTER ( 'Table'[MonthYear], MAX ( 'Table'[MonthYear] ), DESC )
)
)
+ CALCULATE (
[Dropped Out],
FILTER (
ALLSELECTED ( 'Table'[MonthYear] ),
ISONORAFTER ( 'Table'[MonthYear], MAX ( 'Table'[MonthYear] ), DESC )
)
)
3) One for increases
4) one for decreases
Thanks for the answer, @Anonymous.
It works! Although, I will accept @mahoneypat answer as the solution as this solution is a little more straight forward.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |