How can I show the total usage value for the previous month on my BI report?

[Rck7]

Hi friends, 

I have created a column chart  with date slicer from (1-Feb-2017 to 20-sept- 2017) where september is my most recent/current month.  I am trying to create a label which shows the previous month Total( e.g. Month Name: Value). In this case, the label should  should show (August: 83762). Instead, the label is showing the value total of January which is not what I am looking for.

The Measure which I created is considering the previous month from the usage date selected on the slicer. How would I be able to solve this isssue? 

Chart & Measure:



Kindly, help.
Thank you.  

[Greg_Deckler]

Remember this key documentation point on PREVIOUSMONTH:

"This function returns all dates from the previous month, using the first date in the column used as input".

If you can share some sample data then we might be able to more easily help. Typically, you want a separate Date table to use with Time Intelligence functions. Not sure if you have that or not. For example, in your case below, if you selected September and had a related Date table, then that should filter the Date table down to only September records and thus PREVIOUSMONTH would work the way you expect. What is likely going on is that somehow in your setup, January is showing up in your date column as the earliest/first date. Just a guess.

[Rck7]

@Greg_Deckler here is my Table structure looks like:


 
The usage Date column has daily data from(27-Oct-14 to 20-Sept-2017) as shown in the picture above.

What I am looking for is that, I want my (Previous Month label) to show the month which is previous(in this case it is August) to the current month(september) independent of the Date slicer.

For example, if I add the data for the month of October 2017 the previous month label should be able to display the (month name and value) of September which is the previous month of October and so on for the upcoming months. 

Kindly, show me how would I be able to achieve this?
Thank you.

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[sevenhills]

Try this ... 

Previous Month Total = CALCULATE (
    SUM ( TokenRecords[Tokens] ),
    FILTER ( ALL (TokenRecords[UsageDate] )
             , MONTH(TokenRecords[UsageDate]) = MONTH( MAX ( TokenRecords[UsageDate] )) - 1 )
) 

[Rck7]

@sevenhills Thanks for replying.
I have tried ur measure and what I am getting is the sum of all the tokens from the start date until the previous month.

What I am looking to see is just the total number of tokens for the previous month(i.e. the total tokens used for the month which is previous to the current month). In this case it should be 83762 for August(is the previous month of september).

Screenshot:

Kindly, help!!
Thank you. 

  

[Ashish_Mathur]

Hi,

Should previous month be 1 month prior to the dates chosen in the slicer or should it just ignore the slicer and look at the month previous to the maximum month available in your dataset?  Please clarify.  Also, share the link from where i can download your file.

[Rck7]

@Ashish_Mathur thanks for the reply. It should  just ignore the slicer and look at the month previous to the maximum month available in the dataset.

I am using the date slicers to work(or test) creating more charts with my data. But, the file which I would publish later will have no filters.    

Any help would be appreciated. 
Thank you.

[Ashish_Mathur]

Hi,

Try this

1. Drag the Usage date field to the Visual filter.  Apply an advanced filter there to give between dates
2. Here is the formula i wrote

=CALCULATE(SUM(TokenRecords[Tokens]),PREVIOUSMONTH(TokenRecords[UsageDate]))

Download the workbook from here.

[Rck7]

@Ashish_Mathur It works!!! & Thanks for the help. Could You also tell me how can I show the previous Month Name with the value that I am getting?

I am wondering if I have to change the usage date filter to the latest month manually when ever I add data for the upcoming months? 

For example: Currently, as you see I have data until the the september month. At the end of the current month, I will be adding the October month data to the data table. When I do that will the measure automatically show me the previous month value
(i.e. september) or I have to change the Usage date filter manually when I add new month data(latest) to the Data table? 

Thank you. 

[Ashish_Mathur · Accepted solution]

Hi,

You are welcome.  If my reply helped, please mark it as Answer.  I don't think there is a way to show a Month Name in that visualisation.  As regards your second question, try this

In the Usage data filter section, select Relative date filtering > Is in the last > 1 > calendar months > Apply filter.

Hope this helps.

[sevenhills]

I tested with my sample data, before posting the solution ... 
                   (Note: You have the data from the same year, so did not consider that in the formula.)

Last Second Month Total from Selection = CALCULATE (
          SUM ( TokenRecords[Tokens] ),
          FILTER ( ALL (TokenRecords[UsageDate]  ),

                      MONTH(TokenRecords[UsageDate] ) = MONTH( MAX ( TokenRecords[UsageDate] )) - 1 )
           )

You can try temp measure and see for what value it is showing for

          MAX ( TokenRecords[UsageDate] ) -- This should match you max date of your selection

          MONTH( MAX ( TokenRecords[UsageDate]  ))  -- This should match your month of your selection

          MONTH( MAX ( TokenRecords[UsageDate]  )) - 1 -- This should match your Last second month, where we are trying to calculate 

or Share your link where we can download the file and I can see ... Thanks

[sevenhills]

Previous month calculations are explained in this article ... http://www.daxpatterns.com/time-patterns/

PreviousMonth DAX function, as per the https://msdn.microsoft.com/en-us/library/ee634758.aspx, says "Returns a table that contains a column of all dates from the previous month, based on the first date in the dates column, in the current context."

You can also try the solution posted in https://community.powerbi.com/t5/Desktop/DAX-Previous-Month-Function-Not-working/td-p/71094

Thanks