DatesYTD Equivalent to Use with Direct Query

[MB2]

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 dealing with 50M records and I don't want to use Tabular for this instance. Also, this report is also being saved to Power BI Report Server.

I am not completely convinced the issue is with just DirectQuery (as DatesYTD with DirectQuery works fine in Desktop), the issue appears when I open the report via Power BI Report Server: "Function 'DATESYTD' is not supported in DirectQuery mode.
Please try again later or contact support. If you contact support, please provide these details."

Here is my DAX formula using the DatesYTD:

**bleep**. Amount (by Year) = CALCULATE(SUM('Product'[Amount]), DATESYTD(Calender[SnapshotDate]), ALLEXCEPT('Product','Product'[ResultsDescription],'Product'[Member],Branches[Division],Branches[Branch]))

I am using the DAX measure to compare cummulative growth by week, with a new line for each year.

Thanks

[DAX0110]

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 · Accepted solution]

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]
        )
    )

[Greg_Deckler]

Perhaps you could create a column that is the number of days ago, DATEDIFF and NOW are both supported. Then, you could create your measure by filtering on how many days ago.