Need help with filtering from Power BI to SQL Server

[Anonymous]

Hello, I'm having problems finding a way to filter quotes by dates. Quotes can have multiple revisions so I need to show the most recent one between the range of dates. For this I have already a SQL query so in SQL I can actually filter this by date with no problem using startdate and enddate, but I don't know how to make it possible for users to select those dates from Power Bi using the slicer. Here it is:

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '1/1/2019'
SET @enddate = '1/31/2019'

;WITH PrimaryQuoteRevs (quotenumber, created_on) as (
SELECT quote.quotenumber, max(quote.createdon)
FROM quote

WHERE
( quote.createdon between @startdate and @enddate )

GROUP BY quote.quotenumber
), PrimaryQuotes (quoteid, quotenumber, createdon, contact,
resendusername, totalamount, recordurl) as (
SELECT
quote.quoteid,
quote.quotenumber,
quote.createdon,
quote.contact,
quote.resendusername,
quote.totalamount,
quote.recordurl

FROM PrimaryQuoteRevs, quote
WHERE
(PrimaryQuoteRevs.quotenumber = quote.quotenumber)
AND (PrimaryQuoteRevs.createdon = quote.createdon)
)

SELECT * FROM PrimaryQuotes

Any ideas? Thanks!

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could use CALENDAR or CALENDARAUTO to create a date table.

Then create a relationship with quote.quotenumber by date column.

Now you could use date from this calendar table to create a new slicer to filter data by date range

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers#create-slicers

Best Regards,

Lin