topic Re: Determine what status is on date in DAX Commands and Tips

Determine what status is on date

CVanpat91 — Wed, 08 Jun 2022 15:40:22 GMT

I need to be able to determine what the the status is of a location on a given date. For example of the data below I need to bea ble to tell how many types are in each status on a given date.

Example: with the data below I need to be able to do a sum of all types that are in approved status as of the first of a month and year.

date	type	status
1/1/2014	yellow	inactive
2/5/2016	blue	Approved
7/4/2018	red	inactive
1/1/2020	red	Pending
1/2/2020	yellow	pending
2/15/2020	yellow	approved
8/8/2020	blue	inactive
5/8/2021	red	approved

Re: Determine what status is on date

ManguilibeKAO — Sun, 12 Jun 2022 19:52:58 GMT

Hi,

Please, could you mention clearly what you have as input data, and what output you want?

Best regards.

Re: Determine what status is on date

v-cazheng-msft — Mon, 13 Jun 2022 02:50:04 GMT

Hi CVanpat91,

You may try this Measure and make some changes to it per your needs.

CountForFirstOfMonth&Year = VAR specificDate = DATE ( 2020, 11, 15) //specify a date VAR asOfDate = DATE ( YEAR ( specificDate ), MONTH ( specificDate ), 1 ) //get the first day of specific month&year VAR countType = CALCULATE ( COUNT ( 'Table'[type] ), FILTER ( 'Table', LOWER ( 'Table'[status] ) = "approved" && 'Table'[date] < asOfDate ) ) //calculate based on date before the first day of specific month&year RETURN countType

Then, the result will look like this.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun