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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jslade
Frequent Visitor

Help Filtering a Card/ Chart Based on Dynamic Dates

I need to filter a card and chart visual by a date range that is being selected in a slicer.  For example, the user will pick 3/15 in the As Of Slicer and a year in the Year Slicer.  I need to calculate the Fill for the past 15 days of the As Of Slicer for those two visuals.  The other visuals will filter on the next holiday and for the entire year.  I was trying to use Calculation Groups, but I haven't used them before and not sure how to use them as a filter.  Below is the screenshot of my report.  The other option is to do it as a Filtered Measure but I have been getting different errors with it.  I'm guessing I need to do it as a SUMX so that I can get the individule calculations for each date in my chart.  I've been trying to use Varaiables with the filtered measure, but I keep getting errors (usually mullitple values were returned errors).  Below is what I was trying with different calculation functions.

 

Actualized Fill Begin = VAR AsOfDate = SELECTEDVALUE(FactDOR[As Of Date])
VAR YearSel = SELECTEDVALUE(DimDate[Year])
VAR Interval = MAX(FactDOR[Increment])
VAR BeginDate = DATEVALUE(AsOfDate) - Interval
RETURN
CALCULATE(?)

 

jslade_0-1651013833149.png

 

Any Ideas?

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@jslade , if you select a date and want to display a trend of more than the selected date/dates, Then you need an independent date table

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@jslade , if you select a date and want to display a trend of more than the selected date/dates, Then you need an independent date table

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Thank you that helped me get it working.  I didn't have to create the second date table but the video of the measures helped me to get it to work.  My measure worked as:

Actualized Fill = var MaxDate = SELECTEDVALUE(FactDOR[As Of Date])
var MinDate = IF(DAY(MaxDate) < 20, DATE(YEAR(MaxDate), MONTH(MaxDate), 1), DATE(YEAR(MaxDate), MONTH(MaxDate), 15))
RETURN
CALCULATE([Incremental Fill],FILTER('FactDOR', FactDOR[Date] >= MinDate && FactDOR[Date] <= MaxDate))

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.