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

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

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    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.

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