Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

how to write formula when doing query

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

 

 

Smauro

 
 
 

 

 

thanks in advance profs

Hi Profs I am not sure how to change your previous posted code to fit in here

Smauro @AIB

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

4 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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.

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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