Measure conditional on another column

[byr10112]

I have a table that shows the renewal dates of contracts, where I have a column called Renewal Date. I have a separate table called Calendar, where I have the dates and the corresponding fiscal year codes. Below is what the calendar table looks like:

Day	Fiscal Year
28/06/2023	FY23
29/06/2023	FY23
30/06/2023	FY23
01/07/2023	FY24

Below is what the renewals table looks like:

Account ID	Renewal Date
1001	28/06/2023
1002	30/06/2023
1003	02/07/2023

I'm trying to calculate the number of renewals that fall into FY23. Is there a way to create such a measure on the Renewals table?

[v-binbinyu-msft · Accepted solution]

Hi @byr10112 ,

Please create a measure with below dax formula and add it to card visual:

Measure =
VAR cur_fiscal = "FY23"
VAR first_day =
    CALCULATE (
        FIRSTDATE ( 'Calendar'[Day] ),
        'Calendar'[Fiscal Year] = cur_fiscal
    )
VAR last_day =
    CALCULATE ( LASTDATE ( 'Calendar'[Day] ), 'Calendar'[Fiscal Year] = cur_fiscal )
VAR tmp =
    FILTER (
        ALL ( Renewals ),
        [Renewal Date] >= first_day
            && [Renewal Date] <= last_day
    )
VAR ctn =
    CALCULATE ( DISTINCTCOUNT ( Renewals[Account ID] ), tmp )
RETURN
    ctn

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@byr10112 what defines upcoming? 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[byr10112]

In FY23

[parry2k]

@byr10112 set the relationship between date table and renewal table on the date column and then add following measure:

Renewal Count = COUNTROWS ( RenewalTable )

In a table visual, use FY from the Date table and above measure and that will do it.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[byr10112]

In the table I am using, I also have renewals scheduled for the next fiscal years as well. I just want to be able to flag the accounts with upcoming renewals