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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
bc2022
Frequent Visitor

dynamically showing date range in Matrix

Hello,

 

Is there a way to dynamically adjust the date range based on the options listed in switch? My use case is I want to show metrics value within certain year range for different categories in matrix. The problem is each individual category may have different year range data. Currently, the report shows blank cells for those dates without value. So my goal is for below screenshot, the matrix only shows columns having data which start from column Jul-21.

 

1.png

 

The dax I used is :

test = SWITCH(
SELECTEDVALUE(Criteria[Criteria]),
"Option1", FORMAT(SUM(Rating[Value]),"General Number"),
"Option2",FORMAT(SUM(Rating[Value]),"0%"),
"Option3",FORMAT(SUM(Rating[Value]),"0%")
)

 

I can retrieve the start date and end date of each category dataset by using firstdate() and lastdate() function. But no sure how to incorporate them into above dax without breaking existing logics.

 

Many thanks.

 

2 REPLIES 2
amitchandak
Super User
Super User

@bc2022 , Try Field parameters, Make sure none of the measures using +0 or handling of blank or the below option should not be used 

 

Show item with No data new.png

 

Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak 

Thank you for your suggestion. 

I tried this approach but it seems it can't help with my issue. When I created a field parameter for the category field, it just gave me a category slicer without any available options (in my case, it did not list option1,option2, option3 which user can choose) . Also, it seems there is no place I can restrict the date range I mentioned in my question.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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