I’m working on creating a Power BI report where users can input a specific date to drive the metrics displayed in the report. I’m exploring options to achieve this and need some guidance.
Here’s the dataset I’m working with:
I’d like users to be able to enter a report date and see metrics based on that date. For example:
If the user enters “09/03/2024,” the metrics should be:
- Number of Issues Logged: 04
- Number of Issues Completed: 02
- Number of Open Issues: 02
- Number of Issues Logged in the Last Two Days (09/03 – 09/02): 03
If the user enters “09/07/2024,” the metrics should be:
- Number of Issues Logged: 09
- Number of Issues Completed: 03
- Number of Open Issues: 06
- Number of Issues Logged in the Last Two Days (09/07 – 09/06): 01
I attempted to use a date slicer for this purpose, but it seems to default to the minimum date, regardless of the date selected. I’m looking for a way to allow user input for the report date without using Power Apps.
Any suggestions on how I might achieve this functionality in Power BI?
Thank you for your help!
1
You can create a date table and use the date in that table to filter
date=CALENDAR(min('Table'[Logged Date]),max('Table'[Logged Date]))
do not create relationships between these two tables
then create measures
Number of Issues Logged =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( 'Table', 'Table'[Logged Date] <= SELECTEDVALUE ( 'date'[Date] ) )
)
Number of Issues Completed =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[Logged Date] <= SELECTEDVALUE ( 'date'[Date] )
&& 'Table'[Status] = "Completed"
)
)
Number of Open Issues = [Number of Issues Logged]-[Number of Issues Completed]
Number of Issues Logged in the Last Two Days =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[Logged Date] <= SELECTEDVALUE ( 'date'[Date] )
&& 'Table'[Logged Date]
>= SELECTEDVALUE ( 'date'[Date] ) - 1
)
)