cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper IV

## 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 IV

@Ashish_Mathur Both work. Thank you!

Super User

You are welcome.

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

Announcements

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

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors