Forum Discussion

byr10112's avatar
byr10112
Microsoft Employee
3 years ago
Solved

Measure conditional on another column

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
parry2k
Super User
3 years ago

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's avatar
    byr10112
    Microsoft Employee
    3 years ago

    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