Forum Discussion

MB2's avatar
MB2
Advocate I
8 years ago
Solved

DatesYTD Equivalent to Use with Direct Query

I am looking for an expression that can substitute for the DatesYTD function in DAX. The DatesYTD function is not compatible with DirectQuery as per here, but I need to use DirectQuery as I am dealin...
  • MB2's avatar
    MB2
    8 years ago

    Hi DAX0110,

     

    I am glad I asked, because I would not have come up with this. You are a genius! The only change that I had to make was to add MAX and correct my poor spelling of 'Calendar'

     

    Cummulative Amount (by Year) =
    VAR joinDate = MAX( 'Calendar'[SnapshotDate] )
    VAR joinYear = YEAR(MAX('Calendar'[SnapshotDate]) )
    RETURN CALCULATE(
        SUM('Product'[Amount])
        , FILTER(
            ALL( Calendar )
            , YEAR(Calendar[SnapshotDate]) = joinYear
            && Calendar[SnapshotDate] <= joinDate
            )
        , ALLEXCEPT( 'Product'
            ,'Product'[ResultsDescription]
            ,'Product'[Member]
            ,Branches[Division]
            ,Branches[Branch]
            )
        )

DAX0110's avatar
DAX0110
Resolver V
8 years ago

Hi MB2, please try the following to see if it works for you as a replacement for DATESYTD in DirectQuery mode:

 

**bleep**. Amount (by Year) =
VAR joinDate = MAX( 'Product'[JoinDate] )
VAR joinYear = YEAR( joinDate )
RETURN CALCULATE(
    SUM('Product'[Amount])
    , FILTER(
        ALL( Calender )
        , YEAR(Calender[SnapshotDate]) = joinYear
        && Calender[SnapshotDate] <= joinDate
        )
    , ALLEXCEPT( 'Product'
        ,'Product'[ResultsDescription]
        ,'Product'[Member]
        ,Branches[Division]
        ,Branches[Branch]
        )
    )
    

// you'd need to make some mods to this formula: 

//      replace 'Product'[JoinDate] with the actual date column used in the relationship with Calendar

 

MB2's avatar
MB2
Advocate I
8 years ago

Hi DAX0110,

 

I am glad I asked, because I would not have come up with this. You are a genius! The only change that I had to make was to add MAX and correct my poor spelling of 'Calendar'

 

Cummulative Amount (by Year) =
VAR joinDate = MAX( 'Calendar'[SnapshotDate] )
VAR joinYear = YEAR(MAX('Calendar'[SnapshotDate]) )
RETURN CALCULATE(
    SUM('Product'[Amount])
    , FILTER(
        ALL( Calendar )
        , YEAR(Calendar[SnapshotDate]) = joinYear
        && Calendar[SnapshotDate] <= joinDate
        )
    , ALLEXCEPT( 'Product'
        ,'Product'[ResultsDescription]
        ,'Product'[Member]
        ,Branches[Division]
        ,Branches[Branch]
        )
    )