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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
mmunoz17
Frequent Visitor

Select Current Month and Year in Slicer (No filtering needed)


Hi,

I have two fact tables which are related to a Calendar table (built as CALENDARAUTO). One of these fact tables contains dates from past years until current month, and the other one has dates from the same past years until December of current year.

The report use measures obtained from the two fact tables with respect to the dates from Calendar table (YTD and monthly computations, for every year). It is also displayed a segmentation filter with Year and Month from the Calendar table. I would like that Current Year and Current Month will be the default value on the filter for the report.

 

I have seen multiple questions at the community with this same subject, but all of proposed answers do not apply to my case:

  • Creating columns in Power Query (with 2019, 2020, Current Year values) does not work, as I can't create them for the calendar table there (calendar table is created on the report view in DAX). I also can't create these columns for fact tables, as they are two and I need common Year and Month segmentation filters for both of them.
  • YTD measures are needed, so actions like this (https://community.powerbi.com/t5/Desktop/Current-month-default-selection-in-Slicers-so-that-it-will-...) are not useful, as they filter the entire Calendar table and then these YTD computations are not well performed, and measures referring to previous year are seen as BLANK values.
  • Setting Year and Month slicers to MAX values also does not work. It works for Year slicer, but not for Month's (I don't need the max value of month, because December is always displayed for the second table, I need the value of the current month).


I would be willing to detail my case as needed. Any help will be appreciated.

 

Thanks in advance!

Mario

1 ACCEPTED SOLUTION

Hi @mmunoz17 ,

I'm not very clear about your requirement. Could you please provide some sample data (exclude sensitive data) and your expected result with example or screenshot? Thank you.

In addition, please review the content in the following links and hope they can help you resolve the problem.

Year to date calculation

TOTALYTD Cannot get to calculate YTD through Today

YTD of years until today

Best Regards

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

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@mmunoz17 , I doubt there is a way other than default year and month using flag type column. If you date table, these type of columns can help

 

Year Type = Switch( True(),
year([Date])= year(Today()),"This Year" ,
Format([Date],"YYYY")
)

 

Month Type = Switch( True(),
Date([Date])= eomonth(Today(),0),"This Month" ,
[Month]
)

 

Select this month and this year

 

 

Hi @amitchandak, thanks for your quick response!

 

Unfortunately, that doesn't work for me, since doing that filters the dates of the data model exclusively to the current month and year. This way, YTD and relative to previous years measures are not properly computed (they are just BLANK values).

Hi @mmunoz17 ,

I'm not very clear about your requirement. Could you please provide some sample data (exclude sensitive data) and your expected result with example or screenshot? Thank you.

In addition, please review the content in the following links and hope they can help you resolve the problem.

Year to date calculation

TOTALYTD Cannot get to calculate YTD through Today

YTD of years until today

Best Regards

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

I used the idea of the first link you added, and it worked!

Thank you so much,

Mario

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.