Forum Discussion

switchback5's avatar
switchback5
Frequent Visitor
6 years ago

How to filter DirectQuery for Same Day Last Year

I am using DirectQuery to connect to my Snowflake DWH.  I am trying to create a dashboard that has two very similar cards - both contain the sum of total revenue, one for yesterday's date and one for the same date last year.  Creating the one for yesterday is easy enough using a relative date filter, but I cannot figure out how to filter for the same date last year.

 

This would be a whole lot easier if PBI just let me pass custom SQL to Snowflake... Tableau has this ability, why doesn't PBI?

Need Help

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    switchback5 Well since this is DirectQuery, TI is out. So, how about:

    Measure =
  __Date = <your date calculation>
  __LYDate = DATE(YEAR(__Date)-1,MONTH(__Date),DAY(__Date))
RETURN
  SUMX(FILTER('Table',[Date]=__LYDate),[Column])

    Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2. 

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    switchback5 , same day last year is 364 days behind with a date table

    Same day Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Day))

     

    if need you can have date table in power. Check if it supported with snowflake

    https://youtu.be/cQfJ0GmQ5os

    • switchback5's avatar
      switchback5
      Frequent Visitor
      6 years ago

      Tried this, getting a "Couldnt load the data for this visual" error.

       

      "OLE DB or ODBC Error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.."

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi switchback5 

    's measure is greate. I build a table in SQL and connect to Power Bi by Direct Query, then test by his measure. I get the result.

    Result:

    Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.