DAX Count of Cumulative revenue by ID each month for a selected period

[Nick2385]

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! 

[Anonymous · Accepted 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

[Nick2385]

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:

VAR _cumul=

CALCULATE (SUM(

    Table[Revenue]),

        DATESINPERIOD('Calendar)'[Date], MAX('Calendar'[Date]), -12, MONTH

    )

)

RETURN

_cumul

This leads to my issue mentionned in thetopic since in the table I have no rows for certain months. 

But they need to be counted because the cumulative sum adds revenue values for the missing months in the table.

I hope it makes more sense. 

Thanks everyone!

[Nick2385]

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:

VAR _cumul=

CALCULATE (SUM(

    Table[Revenue]),

        DATESINPERIOD('Calendar)'[Date], MAX('Calendar'[Date]), -12, MONTH

    )

)

RETURN

_cumul

This leads to my issue mentionned in thetopic since in the table I have no rows for certain months. 

But they need to be counted because the cumulative sum adds revenue values for the missing months in the table.

I hope it makes more sense. 

Thanks everyone!

[powerbiexpert22]

Hi @Nick2385 ,

can you share some sample data or pbix?

[Anonymous]

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

[ryan_mayu]

could you pls provide the data of Monthly revenue table?

[lbendlin]

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