Forum Discussion
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 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?
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 ctnPlease 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.
5 Replies
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.
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
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.
In FY23
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 ctnPlease 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.
