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 trying to use a date filter for a simple measure. I have data for various days and months between 2020 and 2021 in a table. I'd like to apply a filter for only the year 2020. I already have one filter included and I'd like to add the date filter ontop of my current filter.
So I'm trying to count the rows for the letter A but only in the year 2020.
Solved! Go to Solution.
Hi @HamidBee.
I'm assuming that you have a date table in your data model and that it is marked appropriately. If so, this should be fairly easy with either the DATESBETWEEN or DATESINPERIOD functions. Pick a function and insert the appropriate start and end dates as parameters. Hope this helps!
Hi,
You can simplify your measure to
Count = CALCULATE(COUNTROWS('Table'),'Table'[Letter]="A")
Create a Calendar Table and build a relationship (Many to One and Single) from the Date column of your Table to the Date column of the Calendar Table. Write calculated column formulas in the Calendar table to extract Year, Month name and Month number. Sort Month name by Month number in the Calendar Table. Build slicers for Year and Month name. Select a Year and Month in the slicers.
Hope this helps.
@Ashish_Mathur Nice to see that discussion from our meetings about people swooping in and offering multiple solutions from Super Users is being honored. Does nothing more than confuse users and pad stats. But good job!
Hi @HamidBee.
I'm assuming that you have a date table in your data model and that it is marked appropriately. If so, this should be fairly easy with either the DATESBETWEEN or DATESINPERIOD functions. Pick a function and insert the appropriate start and end dates as parameters. Hope this helps!
Thank you, it worked like a charm. I haven't created a date table though I just used a date column from the same table. Here is an example code of what I used:
Hi @HamidBee. It's good practice to always have a date table in your data models.
Why the error...not really sure and without the pbix would be hard to tell. But I did put your formula through the DAX formatter from SQLBI and you have extra parentheses before DATESBETWEEN.
Count =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( 'Table', 'Table'[Coumn] = "Allowed" ),
(
DATESBETWEEN (
'Table'[Date column],
DATE ( 2020, 01, 01 ),
DATE ( 2021, 01, 01 )
)
)
)
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 |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
115 | |
105 | |
95 | |
79 | |
72 |