Forum Discussion

juhoneyighot's avatar
juhoneyighot
Helper III
1 year ago
Solved

Conditional date formula dax

Hello! I have this data. With Mac Trans Date Column, I want to create a measure that is this Transdate is 1 and 1/2 yr older than a current date then " No Need to Request" elsa "". Thank you ...
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Ok, you can intergrate calculate and filter function as follows:

    SubAgreeCertRequest =
    VAR CutoffDate = TODAY() - 548
    RETURN
    IF (
    CALCULATE(
    MAX(_Facts[TransDate]),
    _Facts[GLName] = "Bank Account - USD",
    _Facts[FH_CurrentYN] = "Yes",
    _Facts[FH_Type] = "28 Legacy AS Transactions"
    ) <= CutoffDate,
    "No Need to Request",
    ""
    )

     

     

    Try the above. 

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

Hi juhoneyighot - you can use the following formula

Status Measure =
VAR CutoffDate = TODAY() - 548 -- Approx. 1.5 years in days (365.25 * 1.5)
RETURN
IF(
MAX('YourTable'[Mac Trans Date]) <= CutoffDate,
"No Need to Request",
""
)

 

use the  above measure to your Power BI report.Place it in a table visual or any context where you want to see the result for each record.Ensure the Mac Trans Date column is properly formatted as a date column.

  • juhoneyighot's avatar
    juhoneyighot
    Helper III
    1 year ago

    rajendraongole1 
    This is the correct Formula SubAgreeCertRequest =
    VAR CutoffDate = TODAY() - 548
    RETURN
    IF (
    MAX(_Facts[TransDate]) <= CutoffDate,
    "No Need to Request",
    ""
    )

    But I have to Filter the following from _Facts table
    _Facts[GLName] = "Bank Account - USD",
    _Facts[FH_CurrentYN]="Yes",
    _Facts[FH_Type]="28 Legacy AS Transactions"

     

    how should I integrate this in the formula?

    • rajendraongole1's avatar
      rajendraongole1
      Super User
      1 year ago

      Ok, you can intergrate calculate and filter function as follows:

      SubAgreeCertRequest =
      VAR CutoffDate = TODAY() - 548
      RETURN
      IF (
      CALCULATE(
      MAX(_Facts[TransDate]),
      _Facts[GLName] = "Bank Account - USD",
      _Facts[FH_CurrentYN] = "Yes",
      _Facts[FH_Type] = "28 Legacy AS Transactions"
      ) <= CutoffDate,
      "No Need to Request",
      ""
      )

       

       

      Try the above.