cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## DAX for CALCULATE Using a Column with a Date as Filter

Hi I have a table that holds a identifier column and a max_eff date column.

I'm trying to do a simple Calculate formula to obtain a count for all the assetnums that have a max_eff date of 1/1/2021, however every result is returning BLANK. I know the issue if the way I am trying to filter on the max_effect column. Any suggestions?
I do have the table connected to my date table and have tried using DATE = 1/1/2021 but that also returned BLANK.

DAX:

``2021 Starting Asset Count = CALCULATE(COUNTA('Program Status'[assetnum]), 'Program Status'[max_effdate] = 1/1/2021)``

TABLE:

Thanks!

1 ACCEPTED SOLUTION
Super User

Hi,

One of these should work

``2021 Starting Asset Count = CALCULATE(COUNTA('Program Status'[assetnum]), 'Program Status'[max_effdate] = DATE(2021,1,1))``
``2021 Starting Asset Count = CALCULATE(COUNTA('Program Status'[assetnum]), FILTER('Program Status','Program Status'[max_effdate] = DATE(2021,1,1)))``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3
Super User

Hi,

One of these should work

``2021 Starting Asset Count = CALCULATE(COUNTA('Program Status'[assetnum]), 'Program Status'[max_effdate] = DATE(2021,1,1))``
``2021 Starting Asset Count = CALCULATE(COUNTA('Program Status'[assetnum]), FILTER('Program Status','Program Status'[max_effdate] = DATE(2021,1,1)))``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper V

@Ashish_Mathur Both work. Thank you!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors