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.

Anonymous's avatar
Anonymous
Not applicable
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.