Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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-20...
dates
Help Requested
query
amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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!

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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"