Forum Discussion

phemson4top's avatar
phemson4top
Frequent Visitor
8 years ago
Solved

DATEDIFF error when used with multiple columns in CALCULATE()

Good day team, In my design, I have two tables Fact_DO_PF and Dim_Date with the following 1:* relationships Dim_Date[DateKey] : Fact_DO_PF[XDateKey]    (Active) Dim_Date[DateKey] : Fact_DO_PF[Last...
Need Help
  • phemson4top's avatar
    phemson4top
    8 years ago

    Hi team,

     

    I got the solution.

    I eventually added a calculated column to the Fact_DO_PF table. I used DateDiff in the calculated column to find different between the two dates in day with dax shown below:

     

    LastDEPInDays = DATEDIFF(DATE(LEFT(Fact_DO_PF[LastDEP_DateKey],4),MID(Fact_DO_PF[LastDEP_DateKey],5,2),RIGHT(Fact_DO_PF[LastDEP_DateKey],2)),RELATED(Dim_Date[dDate]),DAY)

     

    and I used the column to filter my measure.

     

    NoTransaction = CALCULATE(COUNT(Fact_DO_PF[AccountKey]),Fact_DO_PF[LastDEPInDays]>90)

     

    Thanks to all

phemson4top's avatar
phemson4top
Frequent Visitor
8 years ago

Hi team,

 

I got the solution.

I eventually added a calculated column to the Fact_DO_PF table. I used DateDiff in the calculated column to find different between the two dates in day with dax shown below:

 

LastDEPInDays = DATEDIFF(DATE(LEFT(Fact_DO_PF[LastDEP_DateKey],4),MID(Fact_DO_PF[LastDEP_DateKey],5,2),RIGHT(Fact_DO_PF[LastDEP_DateKey],2)),RELATED(Dim_Date[dDate]),DAY)

 

and I used the column to filter my measure.

 

NoTransaction = CALCULATE(COUNT(Fact_DO_PF[AccountKey]),Fact_DO_PF[LastDEPInDays]>90)

 

Thanks to all