Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Help modifying a query

I am currently using a query that pulls data from a server for a selected date range. I am trying to edit it to pull three years worth of data in the following manner:

1-1-2019 thru 4-4-2019

1-1-2020 thru 4-4-2020

1-1-2021 thru 4-4-2021

I tried duplicating and editing  the date portion , but that only gets me a token identifier error. Do I need to duplicate the entire statement three times? Hoping someone can help.  Her is the date portion of the query- (d.timedown between '2019-01-01' and '2019-4-4')"]),
    

4 REPLIES 4
amitchandak
Super User
Super User

@Anonymous , Are you trying to filter in DAX or while getting data from SQL ?

 

In DAX you can have measure like

 

YTD Corrected =
var _max = format(today(),"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),format('Date'[Date],"MMDD")<=_max)

 

or


YTD Corrected =
var _max = format(today(),"MMDD")
return
if(format(max('Date'[Date]),"MMDD")<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])),blank())

 

Anonymous
Not applicable

Thanks for responding, I am pulling from SQL (I think). I am not very familiar with DAX. When I look at your  YTD samples, where would I enter the actual date range, and Would I repeat this command three times? Sorry for my inexperience, and thank you for you patience!

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Anonymous
Not applicable

Here is the query:
Source = Sql.Database("Server", "Operations", [Query="SELECT TOP (100) PERCENT RTRIM(r.Name) AS rName, pr.Type, pr.Sequence, d.ParkRideID, pr.RideName, DATEDIFF(minute, d.TimeDown, d.TimeUp) AS DownTimeMins, d.TimeDown, d.TimeUp, s.Name, RTRIM(d.Reason) AS Reason, #(lf) RTRIM(d.Resolution) AS Resolution, RTRIM(d.Comments) AS Comments, pr.ParkID, p.Name AS Expr1#(lf)FROM dbo.ParkRideDowntimes AS d INNER JOIN#(lf) dbo.ParkRides AS pr ON pr.ID = d.ParkRideID INNER JOIN#(lf) dbo.Parks AS p ON pr.ParkID = p.ID INNER JOIN#(lf) dbo.DowntimeSubtypes AS s ON d.DowntimeSubtypeID = s.ID INNER JOIN#(lf) dbo.Rides AS r ON pr.RideID = r.ID#(lf)WHERE (d.timedown between '2019-01-01' and '2021-4-4')"]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Expr1", "Site"}, {"ParkID", "Site #"}, {"Name", "Subtype"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "TimeDown", "TimeDown - Copy"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"TimeDown - Copy", "Year"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Year", type date}})
in
#"Changed Type"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors