Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello everyone,
I've been struggling with this for 3 weeks and can't seem to find a solution that works.
Here is my problem.
I have 2 tables, one with Monthly revenue by ID and Date, and one Calendar table (with a relationship between both).
I created a measure to have a cumulative revenue for the past 12 months each month by ID.
Now I need a count for each ID each month (1 row = 1) and another onefor each ID whose Cumulative Revenue is not 0 (each row = 1 or 0).
The problem is there is no row in my fact table when there is no revenue that month, but the cumulative shows a row in the table visual and I need to count that (that's where my problem is - it returns a blank).
Here is a table that shows what I get and what my expected result is:
ID | Month | Monthly Revenue | Cumulative Rev. | Count Obtained | Count Expected |
1 | 01/24 | 10 | 10 | 1 | 1 |
1 | 02/24 | No Data | 10 | Blank | 1 |
1 | 03/24 | 10 | 20 | 1 | 1 |
1 | 04/24 | 10 | 30 | 1 | 1 |
2 | 01/24 | 10 | 10 | 1 | 1 |
2 | 02/24 | No Data | 10 | Blank | 1 |
2 | 03/24 | No Data | 10 | Blank | 1 |
2 | 04/24 | 10 | 20 | 1 | 1 |
The objective is to divide
Count of ID with a cumulative Revenue <> 0 for a selected period
by
Count of ID for a selected period.
I hope it's clear, this is not really easy to explain.
Unfortunately I can't share the pibx since this si confidential information.
Thanks a lot everyone!
Solved! Go to Solution.
Hi @Nick2385 ,
This is because you are not clearing the row context and the calculation is only performed on the current row. You can use the ALL function to clear this context. If you also want to calculate the accumulation by ID, you can use the following DAX.
Cumulative sum =
VAR _cumul=
CALCULATE (SUM(
'Revenue'[Revenue]),ALL(Revenue),
DATESINPERIOD('CalendarTable'[Date], MAX(CalendarTable[Date]), -12, MONTH
),Revenue[ID]=MAX(Revenue[ID])
)
RETURN
_cumul
Best regards,
Mengmeng Li
Hi @Nick2385 ,
This is because you are not clearing the row context and the calculation is only performed on the current row. You can use the ALL function to clear this context. If you also want to calculate the accumulation by ID, you can use the following DAX.
Cumulative sum =
VAR _cumul=
CALCULATE (SUM(
'Revenue'[Revenue]),ALL(Revenue),
DATESINPERIOD('CalendarTable'[Date], MAX(CalendarTable[Date]), -12, MONTH
),Revenue[ID]=MAX(Revenue[ID])
)
RETURN
_cumul
Best regards,
Mengmeng Li
Hi everyone,
I appologize for the delay.
I can't give you a proper pbix but I can show you what my tables look like.
-Monthly revenue table:-
Date | ID | Revenue |
01/2024 | 1 | 10 |
03/2024 | 1 | 10 |
04/2024 | 1 | 10 |
01/2024 | 2 | 10 |
04/2024 | 2 | 10 |
-A Calendar table-
-DAX Formula -
Cumulative sum:
Hi everyone,
I appologize for the delay.
I can't give you a proper pbix but I can show you what my tables look like.
-Monthly revenue table:-
Date | ID | Revenue |
01/2024 | 1 | 10 |
03/2024 | 1 | 10 |
04/2024 | 1 | 10 |
01/2024 | 2 | 10 |
04/2024 | 2 | 10 |
-A Calendar table-
-DAX Formula -
Cumulative sum:
Hi @Nick2385 ,
Have you tried this? This may be helpful to you.
Count = COUNTROWS('Table')
Currently, I am unable to reproduce the same issue, if the above dax doesn't work, please provide more details like DAX of Count Obtained, field of visual. The best thing is to provide a demo with the same problem (link to this public file), this will help us to solve the problem better.
Best regards,
Mengmeng Li
could you pls provide the data of Monthly revenue table?
Proud to be a Super User!
I have 2 tables, one with Monthly revenue by ID and Date, and one Calendar table (with a relationship between both).
To report on things that are not there you need to use disconnected tables and/or crossjoins
User | Count |
---|---|
118 | |
66 | |
65 | |
56 | |
50 |
User | Count |
---|---|
181 | |
85 | |
67 | |
62 | |
55 |