Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Rck7
Helper II
Helper II

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

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:
3.png
4.png

Kindly, help.
Thank you.  

1 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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

12 REPLIES 12
Greg_Deckler
Super User
Super User

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.

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler here is my Table structure looks like:

23.png
 
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.

Hi,

 

Share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Try this ... 

 

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

@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:
3.png
Kindly, help!!
Thank you. 

  

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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.

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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. 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

 

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
Super User
Super User

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

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.