Default filter for report for last full month

[IMK]

Hi

I would like our report view to default to last full month.

Meaning that if a user views the report in any day of February, report shows numbers for January.

If user views the report in any day of March, report shows numbers for February and so on.

How do I accomplish this?

[sdjensen]

@IMK - Hi you could try to read this post I created as a reply to another thread. It describes how I using M create a dynamic month column in my periods table.

I you go with this solution you will have to select the value "Dynamic Month -1" in after adding the new column to a slider, visual, page or report filter. It will also add the posibility for your users to select "Dynamic month -2" if they need to go back another month or "This Month" to see the current months data.

[Eric_Zhang]

@IMK

Please check  a step by step demo.

1. Say there's dataset like
    
2. Created a new table, say 'filteredTable'
   
   

   FitleredTable = FILTER('Table','Table'[date]>=(EOMONTH(TODAY(),-2)+1)&&'Table'[date]<(EOMONTH(TODAY(),-1)+1))
   
   
   EOMONTH(TODAY(),-2)+1
   returns the first day of last month
   
   EOMONTH(TODAY(),-1)+1
   returns the first day of current month

Then create visuals against the FitleredTable.

[CheenuSing]

@IMK

1. I am assuming you have a date table with no gaps. 

2. I am also assuming you want to show the chart / report for only previous month at all times. This report / chart will not show for any other months based on any other filter. 

2. Create a Column in the date table  called as MonthSequentialNumber as

     MonthSequentialNumber = year( date in date table ) * 12 + Month( date in date table) - 1

     This will create a unique number for each combination of month and year.

3. Create a measure called CurrentMonthSeqeuntialNumber = Year(Today()) * 12 + Month(today()) - 1

    This will find the MonthSequentialNumber based on todays date. 

4. Create a column in Date table called Show as

      Show = IF (
                          [MonthSequentialNumber ] = [CurrentMonthSeqeuntialNumber ]  - 1, 1, 0
                          ) 

5. Show will  have a value of 0 or 1 in the date table.

6. Assume you have a measure called [Sales] that calculates the sum of Sales . As per your requirment I am assuming your sales records contains future predicted sales.

7. Create a bar chart  where x-axis is Month and Y- axis is Sales measure.

8. In the visual level filter for the bar chart drag the field Show and set the filter condition show items when the value is 1.

9. You will then be able to see the Sales for the previous month.

Should you require further assistance , please do not hesitate to reply to this post.

If this works for you please accept it as a solution and also give kudos.

[schoden]

@CheenuSing 

I know this is an old post but your solution solved my problem.

Cheers

[Habib]

You need to create a calcuated column to find and store last month. Now this new column let's say "Last month" will be the filter for all your visualizations except the slicer for date.

For example you have placed two bar chart and pie charts, filter for these will be new column but for date slicer, there will be no filter.

[MattAllington]

One way I know of is to create a calculate column in your calendar table that identifies the correct month. Eg "latest month". Then use this value from the column in your filter. When the data is refreshed, the calculated column is recalculated and hence it selects the new month, and everything updates. Your calc column needs to have suitable logic to select the correct month of course. 

[IMK]

Yes, I browsed the forum and came to a conclusion that it is calculated column that I need to use but what would the column syntax look like? I didn't find anything that would help with that one...

[Habib]

Try to use PREVIOUSMONTH dax function for new column

[IMK]

I dont seem to get the exact syntax that i would need to use.

If I choose PREVIOUSMONTH, it needs some additional parameters for it between ( and ) marks (forgot those in english..). So what to put there?

I found below link explaining PREVIOUSMONTH but still i don't get the exact syntax to use..

https://msdn.microsoft.com/en-us/library/ee634758.aspx

[IMK]

I tried following syntax

Column = PREVIOUSMONTH([A_Column_Containing_Dates])

but got following error message

A table of multiple values was supplied where a single value was expected

Singel values expected?? Reference in here

https://msdn.microsoft.com/en-us/library/ee634758.aspx

tell's me that the syntax parameter "Dates", is a  column containing dates. So meaning in plural rather than singular????

This is getting a bit frustrating...

[KHorseman]

It means it requires a column of single values per date. A standard date table contains a continuous column of a range of dates with no gaps and no repeated values. That's what should be used for PREVIOUSMONTH. But in any case that's not the correct use of that function, and there's no reason to use it here. This thread appears to be solving the same problem you're working with.

[IMK]

If I try

Column = PREVIOUSMONTH(TODAY())

I get error

The first argument to 'PREVIOUSMONTH' must specify a column

So the argument needs to be a column of type DATE but column with dates of type of DATE is not correct one because it has multiple values. Like you expect a date column to contain...

What the..????

Somebody please!!