Calculated Column Doesn't work for current year only

[RavenQueen0311]

I'm new to Power BI, and trying to learn quickly, but I'm scratching my head at this issue.  I also know this might not be the best way to accomplish what I'm trying to accomplish, so feel free to suggest alternatives.

I have a table of data rolled up by a few factors, include the Date, State, and a couple of other fields.  In each row there is a column called OpenAccounts representing the number of accounts that were in an Open Status on that date from that state, etc.

I have been tasked with creating a year over year comparison where the user can select the date to run the compairson for.  For this field, since the OpenAccounts is the number of accounts already open at a given point of time, I can't add up YTD.  I have to find the records that match the Date being selected.  In order to select that date, a request has been made to use a sliding date slicer, rather than a dropdown. I opted to use a Before Date Slicer.

In order to accomplish this, I created a measure to find the Max selected date:

ThisYearEndDate = MAXX(ALLSELECTED(QBR_DAILYTOTALS[TRANSACTIONDATEPST]), QBR_DAILYTOTALS[TRANSACTIONDATEPST])

This seems to work fine.  So then using this measure, I am trying to get the sum of values where the Month and Day of the TransactionDatePST field match the Month and Day of ThisYearEndDate.  After trying a bunch of different ways to do this, I settled on creating a calculated column as follows:

Open Accounts = IF(
    AND(
        MONTH(QBR_DAILYTOTALS[TRANSACTIONDATEPST]) = MONTH(QBR_DAILYTOTALS[ThisYearEndDate]),
        DAY(QBR_DAILYTOTALS[TRANSACTIONDATEPST]) = DAY(QBR_DAILYTOTALS[ThisYearEndDate])
    ),
    QBR_DAILYTOTALS[OPENACCOUNTS],
    0
)

Then I grab the SUM of that for all the open accounts on a given date.  This works for all Previous Years (2020, 2021) but always returns 0 for 2022.  I cannot for the life of me figure out why.

For Instance, if I have my Date Slicer to 4/1/2022, the numbers are correct for 2020 & 2021, but 2022 returns 0:

As you can see, doing a simple sum of OPENACCOUNTS for that day shows there are values, but the calculated field always sets it to 0.

Does anyone know what is going on here?  Or have a better solution for my issue?

Example Data:

TRANSACTIONDATEPST	STATEPROVINCE	OPENACCOUNTS
4/1/2020	AZ	784
4/1/2020	NV	11,167
4/2/2020	NV	11,174
4/2/2020	AZ	784
4/3/2020	NV	11,181
4/3/2020	AZ	789
4/1/2021	AZ	1,451
4/1/2021	NV	17,140
4/2/2021	NV	17,170
4/2/2021	AZ	1,455
4/3/2021	AZ	1,461
4/3/2021	NV	17,209
4/1/2022	AZ	13,062
4/1/2022	NV	27,288
4/2/2022	AZ	13,087
4/2/2022	NV	27,323
4/3/2022	NV	27,342
4/3/2022	AZ	13,114

[Icey · Accepted solution]

Hi @RavenQueen0311 ,

In your scenario, [ThisYearEndDate] is a measure and [Open Accounts] is a calculated column. Then, the  value of [ThisYearEndDate] will be static when used in [Open Accounts]. The result is always "4/3/2022". The value of calculated column won't change dynamically based on a slicer. 

Then you can find that, on 4/1/2022, the result is FALSE. So it returns 0.

Try to create a measure like so:

Open Accounts Measure = 
CALCULATE (
    SUM ( QBR_DAILYTOTALS[OPENACCOUNTS] ),
    FILTER (
        ALLSELECTED ( QBR_DAILYTOTALS[TRANSACTIONDATEPST] ),
        AND (
            MONTH ( QBR_DAILYTOTALS[TRANSACTIONDATEPST] )
                = MONTH ( QBR_DAILYTOTALS[ThisYearEndDate] ),
            DAY ( QBR_DAILYTOTALS[TRANSACTIONDATEPST] )
                = DAY ( QBR_DAILYTOTALS[ThisYearEndDate] )
        )
    )
)

For more details, check the attachment.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RavenQueen0311]

Fantastic!  Thank you so much for the explanation, Icey.  I've tried your suggestion and it looks to be working.  You've saved me from a terrible fate, haha.

Appreciated!

Raven