Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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 🙂 

 

Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    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,

4 Replies

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    6 years ago

    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.

     

      • v-diye-msft's avatar
        v-diye-msft
        Community Support
        6 years ago

        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,