Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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')"]),
@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())
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.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |