PREVIOUSMONTH without looking into the future

[carlj]

Hi All,

I am a regular reader of this forum, but this is my first time posting a question.

I have a table that lists a count (of users) by month.

I created a measure 'Total Variance' that calculates the difference between a given month and the month before as below:

Total Variance = 
	sum('table'[total_count]) - CALCULATE(
		sum('table'[total_count]),
			PREVIOUSMONTH('table'[month].[Date]))

The table below, showing a subset of the data, shows that while the difference is calculated correctly it also calculates the difference for June 2019, essentially subtracting the figure for May from the (non-existant) June figure.

Is there a way to force this caculation to only go up to May, or whatever the max month is?

When displaying the data in table format I am able to exlude this last line by filtering out blank entries in the total_count column, but this doesn't work when visualizing the data as graph.

Thanks

[v-cherch-msft · Accepted solution]

Hi @carlj 

You may try to add an IF condition in the formula.For example:

Total Variance =
IF (
    SUM ( 'table'[total_count] ) = BLANK (),
    BLANK (),
    SUM ( 'table'[total_count] )
        - CALCULATE (
            SUM ( 'table'[total_count] ),
            PREVIOUSMONTH ( 'table'[month].[Date] )
        )
)

Regards,

[carlj]

Thanks @v-cherch-msft , this is perfect!