Forum Discussion

ariam's avatar
ariam
Regular Visitor
2 years ago
Solved

Converting SQL formula to DAX

I have a matrix table that is broken into 4 quarters using a 'date opened - year' filter on the page, and I need it to output a running total of values from previous years as well and exclude anythin...
  • ariam's avatar
    ariam
    2 years ago

    Nevermind - I figured out the problem I was having, and converting the SQL code was the wrong way to go in this case as it didn't give me my desired output. This is the code I needed:

    Total Records Opened Before and Closed On or After Filtered Year (Including Null Closed Dates) = 
CALCULATE(
    COUNTROWS(YourTableName),
    ALL(YourTableName[YearColumn]),
    (
        ISBLANK(YourTableName[ClosedDate]) ||
        YEAR(YourTableName[ClosedDate]) >= SELECTEDVALUE(YourTableName[YearColumn])
    ),
    YEAR(YourTableName[OpenedDate]) <= SELECTEDVALUE(YourTableName[YearColumn])
)

     

ariam's avatar
ariam
Regular Visitor
2 years ago

Nevermind - I figured out the problem I was having, and converting the SQL code was the wrong way to go in this case as it didn't give me my desired output. This is the code I needed:

Total Records Opened Before and Closed On or After Filtered Year (Including Null Closed Dates) = 
CALCULATE(
    COUNTROWS(YourTableName),
    ALL(YourTableName[YearColumn]),
    (
        ISBLANK(YourTableName[ClosedDate]) ||
        YEAR(YourTableName[ClosedDate]) >= SELECTEDVALUE(YourTableName[YearColumn])
    ),
    YEAR(YourTableName[OpenedDate]) <= SELECTEDVALUE(YourTableName[YearColumn])
)