Forum Discussion

SudhanshuB's avatar
SudhanshuB
New Member
2 years ago

Date Filter

Hello,  I have requirement where I want to fetch the count of the values in each quarter as "Due in Current Quarter" . For exmaple , Please consider the below table : Name Date of Birth Tom ...
Need Help
Tips and Tricks
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi SudhanshuB,

 

Source:

 

Result:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnPVdJRMiwuUfBKzFMwMlGK1YlW8kgsKqpUAIqblGQo+CYWwcT9MpOzgaJGlmDhSrBwLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Date of Birth" = _t]),
    TransformedDate = Table.TransformColumns(Source, {{"Date of Birth", each Text.Combine(List.Transform(Text.Split(_, " "), (a)=>
     List.Accumulate(
         List.Buffer({"st", "nd", "rd", "th"}),
         a,
         (s,c)=> Text.Replace(Text.Trim(s), c, "") )), " "), type text }}),
    TransformedDateType = Table.TransformColumns(TransformedDate, {{"Date of Birth", each Date.FromText(_, [Format="%d MMM yy", Culture="en-US"]), type date}}),
    Ad_CurrentYear = Table.AddColumn(TransformedDateType, "Current Year", each Date.Year(DateTime.LocalNow()), Int64.Type),
    Ad_YearQuarters = List.Accumulate(
    List.Buffer({1..4}),
    Ad_CurrentYear,
    (s,c)=> Table.AddColumn(s, "Q"& Text.From(c) & "-" & Text.From(Date.Year(DateTime.LocalNow())), each 
               if not ([Date of Birth] < Date.From(DateTime.LocalNow()))
               and Date.Year([Date of Birth]) = Date.Year(DateTime.LocalNow())
               and Date.QuarterOfYear([Date of Birth]) = c then 1 else 0, Int64.Type) ),
    GroupedRows = Table.Group(Ad_YearQuarters, {"Current Year"}, {{"Detail", each _, type table},
     {"Q1-" & Text.From(Date.Year(DateTime.LocalNow())), each List.Sum(Table.ToColumns(Table.SelectColumns(_, {"Q1-" & Text.From(Date.Year(DateTime.LocalNow()))})){0}), Int64.Type},
     {"Q2-" & Text.From(Date.Year(DateTime.LocalNow())), each List.Sum(Table.ToColumns(Table.SelectColumns(_, {"Q2-" & Text.From(Date.Year(DateTime.LocalNow()))})){0}), Int64.Type},
     {"Q3-" & Text.From(Date.Year(DateTime.LocalNow())), each List.Sum(Table.ToColumns(Table.SelectColumns(_, {"Q3-" & Text.From(Date.Year(DateTime.LocalNow()))})){0}), Int64.Type},
     {"Q4-" & Text.From(Date.Year(DateTime.LocalNow())), each List.Sum(Table.ToColumns(Table.SelectColumns(_, {"Q4-" & Text.From(Date.Year(DateTime.LocalNow()))})){0}), Int64.Type}
})
in
    GroupedRows