How to filter DirectQuery for Same Day Last Year

[switchback5]

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?

[v-rzhou-msft]

Hi @switchback5 

Greg_Deckler'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. 

[amitchandak]

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

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

[Greg_Deckler]

@switchback5 Which one? Both? Last year is not always -364 days (leap years and such)

[Greg_Deckler]

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