topic Issue in writing DAX measure in DAX Commands and Tips

Issue in writing DAX measure

Anony_mous — Fri, 23 Dec 2022 20:47:30 GMT

Can you please help me to write dynamic DAX measure? I have the date and training validity column in a table. If the date is within that 2 years or 3 years training validity it should show the training status as "completed" if it exceeds 2 years or 3 years it should show the training status as "not completed" I want to know what is the count of training status "completed" and "not completed" during a given period and categorize them meanwhile I have to use TODAY() function in that measure I am using a date in a slicer and by changing the date, I can change the training validity in that given period.

Thank you so much for any given any help!

Date Training Validity Training Status

10-Jan-20 2

20-Jan-20 3

21-Jul-20 3

21-Mar-20 2

22-Mar-21. 3

2-Apr-21 3

3-Apr-21 2

Re: Issue in writing DAX measure

Greg_Deckler — Fri, 23 Dec 2022 23:37:46 GMT

Anony_mous Maybe:

Training Status Measure = VAR __Date = MAX('Table'[Date]) VAR __Years = MAX('Table'[Training Validity]) VAR __ValidTil = DATE(YEAR(__Date) + __Years, MONTH(__Date), DAY(__Date)) VAR __Result = IF(__ValidTil >= TODAY(), "completed", "not completed") RETURN __Result

If it is OK if it is valid until the end of the month then you could do this:

Training Status Measure = VAR __Date = MAX('Table'[Date]) VAR __Years = MAX('Table'[Training Validity]) VAR __ValidTil = EOMONTH(__Date, -12 * __Years) VAR __Result = IF(__ValidTil >= TODAY(), "completed", "not completed") RETURN __Result

Re: Issue in writing DAX measure

v-jianboli-msft — Wed, 28 Dec 2022 03:08:47 GMT

Hi Anony_mous ,

Approve with Greg_Deckler , You can calculate the training status by using these DAX.

However, You said "I am using a date in a slicer and by changing the date, I can change the training validity in that given period"

What does it mean? What is a given period and how to change the training validity?

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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