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.
I am looking to show total hours for the last three months for a specific department.
I have matrix visual that totals hrs over the splicer date range.
I have hrs values that I need to display on the visual outside the splicer dates.
I have created a Auto Calendar date table (make as date table) and set up a relationship between the Calendar Date table to a start date on my primary hrs value table. Both date columns are set to DATE:dd/mm/yyyy
Using the measure below does not filter the values for the previous month.
The formula for a standard table is not filtering by the date correctly. The totals should be 15 and 20hrs
Hi @DuskyLeaf,
It seems like you are working with direct query connection mode tables, I'd like to suggest you use date function to manually define the filter range instead of use time intelligence functions: (time intelligence functions are based on the hidden calendar table, direct query connection mode will affect it)
previous Month =
VAR currDate =
MAX ( 'sCALENDAR'[Date] )
RETURN
CALCULATE (
SUM ( 'msnfp_participationschedule'[Time Worked] ),
FILTER (
ALLSELECTED ( 'msnfp_participationschedule' ),
[Date]
>= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
&& [Date] <= currDate
)
)
Regards,
Xiaoxin Sheng
... the tables are DirectQuery except sCalendar Table which is import
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
106 | |
79 | |
71 | |
66 |
User | Count |
---|---|
141 | |
107 | |
100 | |
82 | |
74 |