Running totals add non existent months

[jesusrincon]

Hi everyone. I'm trying to create a running total calculation using the quick measure feature. My data looks like this:

ID	LEADSTATUS	LEADSTATUSDATE	INVALIDJUMP
1	Developing	02-01-2019	0
2	Nurturing	02-10-2019	0
3	Marketing	05-11-2019	1

In this case I'm trying to create a view using the matrix, and the matrix should look like this:

The numbers I'm getting here are correct and they're working fine. However, you can see there are additional months, in this case it goes up to December 2019. The LEADSTATUSDATE field has a max value of today everyday, although is not unique. I'm using this DAX for the running totals calculations

Valid Statuses running total in Month = 
CALCULATE(
	[Valid Statuses],
	FILTER(
		CALCULATETABLE(
			SUMMARIZE(
				'CYX GET_LEADSTATUSHISTORY',
				'CYX GET_LEADSTATUSHISTORY'[LEADSTATUSDATE].[MonthNo],
				'CYX GET_LEADSTATUSHISTORY'[LEADSTATUSDATE].[Month],
                                'CYX GET_LEADSTATUSHISTORY'[LEADSTATUSDATE].[Year]

			),
			ALLSELECTED('CYX GET_LEADSTATUSHISTORY')
		),
		ISONORAFTER(
                        'CYX GET_LEADSTATUSHISTORY'[LEADSTATUSDATE].[Year], MAX('CYX GET_LEADSTATUSHISTORY'[LEADSTATUSDATE].[Year]), DESC,
			'CYX GET_LEADSTATUSHISTORY'[LEADSTATUSDATE].[MonthNo], MAX('CYX GET_LEADSTATUSHISTORY'[LEADSTATUSDATE].[MonthNo]), DESC,
			'CYX GET_LEADSTATUSHISTORY'[LEADSTATUSDATE].[Month], MAX('CYX GET_LEADSTATUSHISTORY'[LEADSTATUSDATE].[Month]), DESC
		)
	)
)

Where Valid Statuses is

Valid Statuses = CALCULATE(COUNT('CYX GET_LEADSTATUSHISTORY'[ID]),'CYX GET_LEADSTATUSHISTORY'[INVALIDJUMP] = 0)

I would like to have the number of valid statuses by lead status date and by lead status in a matrix way, as I'm showing above. The numbers are fine and the only issue I have right now is that the visual matrix shows invalid months. I don't have a date dimension, I don't know if it's required, but the field LEADSTATUSDATE is recognized as a hierarchy date.

Is there any way to limit the visual or to modify the DAX for these purposes? Thanks!

[PaulDBrown]

@jesusrincon 

try using this measure:

measure = IF(MAX(YEAR(‘yourtable’[LEADSTATUSUPDATE])) = YEAR(TODAY()) && MAX(MONTH(‘yourtable’[LEADSTATUSUPDATE])) > MONTH(TODAY()), BLANK(), [Valid statuses running total in  month]

PS. A date dimension table is generally/overwhelmingly considered “best practice”

[jesusrincon]

I've tried your solution and the data looks the same. Also, I've changed the mapping to a Calendar Table for these purposes. Got the same issue than using only the leadstatusdate.

I don't know if there's a way to limit only the view. I've changed the view to a normal table to see if there's any issues with the data, and got something like this

Where Measure 2 is the measure you recommended me to use. I'm getting the extra data directly from the running totals calculation, I believe

[PaulDBrown]

@jesusrincon 

If you have added a calendar table, what is the max date value? If it goes until the end of the year, you can use the if statement I mentioned to limit the cumulative values to this month and year using the calendar tabe to filter and as your row values or x axis. 

Here is a screenshot to illustrate it:

[jesusrincon]

The max value is TODAY() as function. The date table was created like this:

Calendar = CALENDAR(MIN('GET LEAD STATUS'[LEADSTATUSDATE]),TODAY())

Your solution kinda works but there's another issue. The numbers become fixed and not in a running totals way. Also, now I have previous data which should be unexistant.

As you can see, this cohort only has data from 2019 March from Cohort 201903 and 2019 April for Cohort 201904 but shows data from months backwards that shouldn't even exist. Also the numbers on the values table are like the maximum values we get on the second table. 

The bottom table has the correct numbers, but shows additional months that I can't limit. The top table has inconsistent numbers and shows data for months on which the value hasn't even come, but it stops on todays month.

I'm using these DAX for each value on the tables:

Top table DAX:

Cummulative Count (IF Statement) = 
var cumulative = CALCULATE(COUNT('GET LEAD STATUS'[ID]),'GET LEAD STATUS'[INVALIDJUMP] = 0,
FILTER(ALL('Calendar'),'Calendar'[Date]<=TODAY()))

Return IF(MAX('Calendar'[Date].[Year]) = YEAR(TODAY()) && MAX('Calendar'[Date].[MonthNo])>MONTH(TODAY()),BLANK(),
cumulative)

Bottom table DAX:

Valid Statuses running total in Month = 
CALCULATE(
	[Valid Statuses],
	FILTER(
		CALCULATETABLE(
			SUMMARIZE(
				'Calendar',
                'Calendar'[Date].[Year],
				'Calendar'[Date].[MonthNo],
				'Calendar'[Date].[Month]
			),
			ALLSELECTED('GET LEAD STATUS')
		),
		ISONORAFTER(
            'Calendar'[Date].[Year], MAX('Calendar'[Date].[Year]),DESC,
			'Calendar'[Date].[MonthNo], MAX('Calendar'[Date].[MonthNo]), DESC,
			'Calendar'[Date].[Month], MAX('Calendar'[Date].[Month]), DESC
		)
	)
)

This is now using a Date table as recommended

Thanks for all your help so far

[PaulDBrown]

@jesusrincon 

The DAX you are using in the top table, [Cumulative Count (If statement)], does not provide cumulative values (it is counting values up to TODAY()). 

You need to use MAX in your filter instead of TODAY.

See the following example: