Previous Month calculation (only include months from current year where we have data)

[Anonymous]

Hi all, 

I'm having a bit of a trouble with creating some measures correctly. 

I want to have a bar-chart visualization of change in percentage for current month vs. previous month. 

In the data I've created some sample data which has values in the months between january 2019 to november 2019 (no data for december 2019). However, the previous month measure calculates the november 2019 value and uses this so that in the bar chart I get a -100% change since previous month for december 2019. 

Anyone know a way to sort this, so that I only calculate previous months where I have data for current month? 

Here is some screenshots of current state: 

The measures I got at the moment is: 

Total Value = SUM ( FACT_Test[SampleData] )
Previous Month = CALCULATE ( [Total Value] ; PREVIOUSMONTH (DIM_Date[Date] ))

% Change = DIVIDE ( [Total Value] ; [Previous Month] ; BLANK() ) -1

Thanks 🙂 

[v-diye-msft]

Hi @Anonymous 

You might consider creating pbix file that will contain some sample data, upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.

I'd like to draw it up for you.

[Anonymous]

Hi @v-diye-msft, 

Thanks for your reply. 

Here is the link for the .pbix: 

https://1drv.ms/u/s!AnO2QNNGszZMjWorRLvE2Zqriy4y?e=oA6AFb

[v-diye-msft · Accepted solution]

Hi @Anonymous 

I edited your measures with adding IF function, please kindly check whether it works for you:

Previous Month = IF(ISBLANK([Total MonthlyMockUp]),BLANK(),CALCULATE( [Total MonthlyMockUp] , PREVIOUSMONTH( 'Date'[Date] )))

% change = IF(ISBLANK([Previous Month]),BLANK(),DIVIDE( [Total MonthlyMockUp] , [Previous Month] , BLANK() ) - 1)

 

Pbix attached,

[Anonymous]

Thank you @v-diye-msft 

This works 🙂