Determine what status is on date

[CVanpat91]

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

[v-cazheng-msft · Accepted solution]

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

[ManguilibeKAO]

Hi,

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

Best regards.