Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Profs
could you help me on this?
I hope the last 3 months lastest date and current month all dates to be filtered into the query, that is for the current month the data all will be included, then include May and April latest date also. in the future, it will be the same current month all data, then the latest date of the previous months, eg June 22 and May 25.
how to filter in the query process, I do not want to load in all the data, then use dax to get the result...
the data is too big
thanks in advance profs
Hi Profs I am not sure how to change your previous posted code to fit in here
@Smauro @AIB
Solved! Go to Solution.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Here is the codes in 'Advanced Editor'.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdK7CsMwDAXQXymeQ9EjfuQ/Ch1CMB0yNdChHfL5TVxZptagyQdf+eJ5dugGR8fwMfd1fVIGvtxen8d23bf37pZhLufnIChC6BFXFBuKPRoF0aiIxh55WYfEcAbqTRAzNTP1JorBoAhDj1INY0VkCpgEMShiU0Bp53xXjRszmDhEQciq0OQhiSJQRTaQq4pNmcrxFF7kiXwGUzl6QWW/n0I0KlSVmkpGRVHkVZE3KklTNTBksIGToNSQySsdlZu8KjR5hKKIVNH/t1q+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Content = _t, Month = _t, Day = _t, Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Content", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"Name", type text}}),
Custom1 = Table.SelectRows(#"Changed Type",each let d=[Day],m=Date.Month(DateTime.LocalNow()),n=[Month] in n=m or
(
(m-n)=1 and
d=Record.Field( Table.Max(Table.SelectRows(#"Changed Type",each [Month]=n), "Day"),"Day")
)
or
(
(m-n)=2 and
d=Record.Field( Table.Max(Table.SelectRows(#"Changed Type",each [Month]=n), "Day"),"Day")
)
)
in
Custom1
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Here is the codes in 'Advanced Editor'.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdK7CsMwDAXQXymeQ9EjfuQ/Ch1CMB0yNdChHfL5TVxZptagyQdf+eJ5dugGR8fwMfd1fVIGvtxen8d23bf37pZhLufnIChC6BFXFBuKPRoF0aiIxh55WYfEcAbqTRAzNTP1JorBoAhDj1INY0VkCpgEMShiU0Bp53xXjRszmDhEQciq0OQhiSJQRTaQq4pNmcrxFF7kiXwGUzl6QWW/n0I0KlSVmkpGRVHkVZE3KklTNTBksIGToNSQySsdlZu8KjR5hKKIVNH/t1q+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Content = _t, Month = _t, Day = _t, Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Content", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"Name", type text}}),
Custom1 = Table.SelectRows(#"Changed Type",each let d=[Day],m=Date.Month(DateTime.LocalNow()),n=[Month] in n=m or
(
(m-n)=1 and
d=Record.Field( Table.Max(Table.SelectRows(#"Changed Type",each [Month]=n), "Day"),"Day")
)
or
(
(m-n)=2 and
d=Record.Field( Table.Max(Table.SelectRows(#"Changed Type",each [Month]=n), "Day"),"Day")
)
)
in
Custom1
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Can you paste a sample, btw do you have a year column? it might be useful in Jan, Feb where you actually need to look at months 1, 12 and 11 or 2, 1 and 12
Many Thanks
Mariusz
Hi Prof
actually there is no year column, but I think maybe later they will name the year inside, currently no need
https://1drv.ms/x/s!Am9VKMj_rOZJoHcFQJNqyLY5j2CJ?e=xmLIHi
here is the excel, thanks in advance Prof @Mariusz