cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sm95
Frequent Visitor

How to get visual to show previous month data by default and filter other dates as well with slicer?

Hi All,

I have requirement wherein I need to show values in my Power BI report based on the date selection.

  • The requirement is for the report to always default to the value based on the previous month from today. i.e.  if current month is Dec 2020, the report should show Nov 2020 data by default.
  • The report consists of some date slicers as well like Year and Month. The user must be able to change the date from the default previous month to any date using slicer as per their need and see the respective values as well.

I tried creating a DAX measure as below. Though the data shows the correct previous month value by default, the visuals always show (Blank) whenever I try to select any other date using slicer, even though those months have data.

Last Month Count =

VAR default_date =

    MAX(Table1[Year_Month])-1

RETURN

    IF (ISFILTERED (Table1[Month Name]),DISTINCTCOUNT(Table1[Project Name]),

        CALCULATE (DISTINCTCOUNT(Table1[Project Name]),FILTER (Table1,Table1[Year_Month]=default_date )

    )

Attached the images of the data and output in PBI for reference. 

Please assist with your suggestions that can help resolve the same.

Thanks!


Sample Data.PNGCorrect Default Value Shown in PBI.PNGFilter selection issue.PNG

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@Sm95 

I am afraid you cannot use date hierarchy in this slicer. As workaround, if you want to show the slicer in Year+MonthName format. You may create a calculated column and use it as slicer. 

YearMonth = FORMAT([Month Name],"YYYYMMM")

 

Then use ISFILTERED ('Table'[YearMonth]) in the measure.

V-pazhen-msft_0-1609914536145.png


Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

 

 

View solution in original post

6 REPLIES 6
V-pazhen-msft
Community Support
Community Support

@Sm95 

I am afraid you cannot use date hierarchy in this slicer. As workaround, if you want to show the slicer in Year+MonthName format. You may create a calculated column and use it as slicer. 

YearMonth = FORMAT([Month Name],"YYYYMMM")

 

Then use ISFILTERED ('Table'[YearMonth]) in the measure.

V-pazhen-msft_0-1609914536145.png


Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

 

 

View solution in original post

V-pazhen-msft
Community Support
Community Support

@Sm95 
You have used ISFILTERED (Table1[Month Name]), you should change it to Year_Month column according to your dax.

Last Month Count =
VAR default_date =
MAX('Table'[Year_Month])-1
Return
IF (ISFILTERED ('Table'[Year_Month]),DISTINCTCOUNT('Table'[Project Name]),
CALCULATE(DISTINCTCOUNT('Table'[Project Name]),FILTER('Table',[Year_Month]=default_date)))
 
 
V-pazhen-msft_0-1609837483495.png

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

@V-pazhen-msft ,

I have given the ISFILTERED (Table1[Month Name]) because I wanted the users to use that field in the filter, since it shows the slicer elements in the attached image format. I have given the data type of Year_Month field as Whole number, since I have to take the MAX of it and subtract 1. So, can't show the individual date hierarchy in slicer using the Year_Month field. Is there any way I can make the Year_Month slicer in the below image format? If so, then I believe your suggestion would work in my case.

Thanks!!

Slicer.PNG

 

amitchandak
Super User IV
Super User IV

@Sm95 , Please use date table and time intelligence and date table

 

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Next month value = CALCULATE(sum(''Table''[total hours value]),nextmonth('Date'[Date]))
Next to next month value = CALCULATE(sum(''Table''[total hours value]),nextmonth(dateadd('Date'[Date],1,MONTH)))
previous to previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth(dateadd('Date'[Date],-1,MONTH)))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

Also check why TI can fail - https://youtu.be/OBf0rjpp5Hw



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Sm95
Frequent Visitor

@amitchandak ,

 

Thanks for the response.Is it mandatory for this functionality to work only if a date table is created separately? Is there any way using the DAX, that I can achieve this without the date table  i.e. within the original table itself? The functionality to fetch the default value works, but only the filtering part seems to fail right now.

 

Thanks!! 

@Sm95 , time intelligence and date table is best way.

If you choose a date , assume from your table in a slicer.

Now to get last month's data you need to use all as slicer already restricted date. All will come with its own setup things you need to work with

 

example auuming month name is a date

measure =
var _1 = eomonth(maxx(allselected(Table1),Table1[monthname]),-1)
return
CALCULATE (DISTINCTCOUNT(Table1[Project Name]),FILTER (all(Table1[monthname]),eomonth(Table1[monthname],0)=_1 ))

 

or

 

 

measure =
var _1 = eomonth(maxx(allselected(Table1),Table1[monthname]),-1)
return
CALCULATE (DISTINCTCOUNT(Table1[Project Name]),FILTER (all(Table1),eomonth(Table1[monthname],0)=_1 ))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.