Forum Discussion
Solved
DAX Formula
I had this below table.
| Course | ABC1 | ABC2 | ABC3 |
| Duration | 36 Months | 24 Months | 18 Months |
| Asst Manager | Y | N | Y |
| AD | Y | N | Y |
| CEO | N | Y | N |
| COO | N | N | Y |
| Manager | Y | Y | N |
to which after transposing I converted to below
| Job Post | Courses | Duration | Completion |
| Asst Manager | ABC1 | 36 Months | Y |
| AD | ABC1 | 36 Months | Y |
| CEO | ABC1 | 36 Months | N |
| COO | ABC1 | 36 Months | N |
| Manager | ABC1 | 36 Months | Y |
| Asst Manager | ABC2 | 24 Months | N |
| AD | ABC2 | 24 Months | N |
| CEO | ABC2 | 24 Months | Y |
| COO | ABC2 | 24 Months | N |
| Manager | ABC2 | 24 Months | Y |
| Asst Manager | ABC3 | 18 Months | Y |
| AD | ABC3 | 18 Months | Y |
| CEO | ABC3 | 18 Months | N |
| COO | ABC3 | 18 Months | Y |
| Manager | ABC3 | 18 Months | N |
I then merged it with another table my table loooks like this now
| Job Post | Courses | Duration | Completion | Completion Date |
| Asst Manager | ABC1 | 36 Months | Y | 12/02/2015 |
| AD | ABC1 | 36 Months | Y | 18/02/2018 |
| CEO | ABC1 | 36 Months | N | 17/08/2020 |
| COO | ABC1 | 36 Months | N | 09/10/2012 |
| Manager | ABC1 | 36 Months | Y | 12/02/2017 |
| Asst Manager | ABC2 | 24 Months | N | 08/02/2019 |
| AD | ABC2 | 24 Months | N | 01/08/2020 |
| CEO | ABC2 | 24 Months | Y | 09/01/2012 |
| COO | ABC2 | 24 Months | N | 12/02/2021 |
| Manager | ABC2 | 24 Months | Y | 02/02/2015 |
| Asst Manager | ABC3 | 18 Months | Y | 01/02/2018 |
| AD | ABC3 | 18 Months | Y | 07/08/2020 |
| CEO | ABC3 | 18 Months | N | 09/10/2017 |
| COO | ABC3 | 18 Months | Y | 12/02/2017 |
| Manager | ABC3 | 18 Months | N | 08/02/2019 |
The data shows that each post has completed the courses and have dates with them showing when they completed. The duration of test shows how long will it be valid. So I want to have two columns or measures. One will show the date with respect to duration and date the compliance of each job. My outcome should look like below table.
| Job Post | Courses | Duration | Completion | Completion Date | Valid Date of Test | Test take |
| Asst Manager | ABC1 | 36 Months | Y | 12/02/2015 | 12/02/2018 | Non compliant |
| AD | ABC1 | 36 Months | Y | 18/02/2018 | 18/02/2021 | Non compliant |
| CEO | ABC1 | 36 Months | N | 17/08/2020 | 17/08/2023 | Compliant |
| COO | ABC1 | 36 Months | N | 09/10/2012 | 09/10/2015 | Non compliant |
| Manager | ABC1 | 36 Months | Y | 12/02/2017 | 12/02/2020 | Non compliant |
| Asst Manager | ABC2 | 24 Months | N | 08/02/2019 | 08/02/2021 | Non compliant |
| AD | ABC2 | 24 Months | N | 01/08/2020 | 01/08/2022 | Compliant |
| CEO | ABC2 | 24 Months | Y | 09/01/2012 | 09/01/2014 | Non compliant |
| COO | ABC2 | 24 Months | N | 12/02/2021 | 12/02/2023 | Compliant |
| Manager | ABC2 | 24 Months | Y | 02/02/2015 | 02/02/2017 | Non compliant |
| Asst Manager | ABC3 | 18 Months | Y | 01/02/2018 | 01/02/2020 | Non compliant |
| AD | ABC3 | 18 Months | Y | 07/08/2020 | 07/08/2022 | Compliant |
| CEO | ABC3 | 18 Months | N | 09/10/2017 | 09/10/2019 | Non compliant |
| COO | ABC3 | 18 Months | Y | 12/02/2017 | 12/02/2019 | Non compliant |
| Manager | ABC3 | 18 Months | N | 08/02/2019 | 08/02/2021 | Non compliant |
Hamdan1234 you can use following two measures to achieve what you need after the very final merge
ValidUntil = VAR _0 = ADDCOLUMNS('Table',"ValidTill", 'Table'[Completion Date]+(CONVERT(LEFT('Table'[Duration],2),INTEGER)*DIVIDE(365,12))) RETURN MAXX(_0,[ValidTill]) isValidNow = SWITCH(true(),[ValidUntil]<=TODAY(),"Non COmpliant","Compliant")
1 Reply
Hamdan1234 you can use following two measures to achieve what you need after the very final merge
ValidUntil = VAR _0 = ADDCOLUMNS('Table',"ValidTill", 'Table'[Completion Date]+(CONVERT(LEFT('Table'[Duration],2),INTEGER)*DIVIDE(365,12))) RETURN MAXX(_0,[ValidTill]) isValidNow = SWITCH(true(),[ValidUntil]<=TODAY(),"Non COmpliant","Compliant")