Forum Discussion

a_fman's avatar
a_fman
Icon for Helper I rankHelper I
9 years ago
Solved

Dataset DAX Query Size Reduction - Swap 18 months for 6 months back, plus 6 months of prior year?

I currently have a dataset that is non-dynamic in terms of what gets pulled in. Every months I change the earliest data date forward by a month so that I always have the most recent 18 months of data...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    a_fman

     

    you can apply a date filtering in Query Editor

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdDRDcQgDAPQXfiuVBJCKLNU3X+NK5xsnXW/T8aKue9iZ7XTq/VyFLvKc2xykE9QA0UFxX+qs2uAEtSYGkyRLnYlaJJwl1UegXrj9e4gXm+Brp3Kl3qVjYt8yEZNBSh0o6aSqS4bF7UmG5UmH6Zs3GSycZ9qslG7dmr8/sR346L2/sTzAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNMonths([Date], 18) or Date.IsInCurrentMonth([Date])),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date] <= Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-12) or Date.IsInPreviousNMonths([Date], 6) or Date.IsInCurrentMonth([Date])) 
in
    #"Filtered Rows1"
Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
9 years ago

a_fman

 

you can apply a date filtering in Query Editor

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdDRDcQgDAPQXfiuVBJCKLNU3X+NK5xsnXW/T8aKue9iZ7XTq/VyFLvKc2xykE9QA0UFxX+qs2uAEtSYGkyRLnYlaJJwl1UegXrj9e4gXm+Brp3Kl3qVjYt8yEZNBSh0o6aSqS4bF7UmG5UmH6Zs3GSycZ9qslG7dmr8/sR346L2/sTzAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNMonths([Date], 18) or Date.IsInCurrentMonth([Date])),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date] <= Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-12) or Date.IsInPreviousNMonths([Date], 6) or Date.IsInCurrentMonth([Date])) 
in
    #"Filtered Rows1"
a_fman's avatar
a_fman
Icon for Helper I rankHelper I
9 years ago

Made a couple minor tweaks but this worked perfectly, thanks!

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc/LDcMwEAPRXnQ2YO3qX4vh/ttIlgqQ0fWBB87zJLvt9mw1XamM9F5PckiXFEiTVEiVNEiRdIhLBsQkE5Il6y++JJZBcxNe+35tuO379m/UQhrTJJVpksI0iTNNYkyTZKaF2GKaZDJNMo40UT/SRO1IE1Wm9ZDCNIkzTWJMk2SmhSyWBUyGBQx2BXRmBbSjKqQeUSHlaApxJo04x6KA79n3Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", let latest = List.Max(#"Changed Type"[Date]) in each Date.IsInPreviousNMonths([Date], 18) or [Date] = latest),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date] <= Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-12) or Date.IsInPreviousNMonths([Date], 6) or Date.IsInCurrentMonth([Date]))
in
#"Filtered Rows1"