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 :

NameDate of Birth
Tom1st Jan 24
Harry 4th Mar 24
Nick29th May 24

In the above table , whoesver Date of Birth is passed with respect to current date/today's date within the current quarter , the count should not include them . Let say for Q1, the count should be 1 as 1st Jan 24 is in past and 29th May 24 is in Q2. Similarly, For Q2 the count should be 1 for "Due in current quarter". 

Please suggest . 

Need Help
Tips and Tricks

5 Replies

  • CidcleyBarbosa's avatar
    CidcleyBarbosa
    Advocate IV
    2 years ago

    Hi SudhanshuB 
    This is one way of doing it:

    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]),
    #"Changed Type" = Table.TransformColumns(Source, {{"Date of Birth", each Date.From(DateTimeZone.From(Text.Select(Text.BeforeDelimiter(_, " "), {"0".."9"}) & " " & Text.BetweenDelimiters(_, " ", " ") & " " & Text.End(_, 2))), type date}}),
    #"Added Date Filter" = Table.AddColumn(#"Changed Type", "Date Filter", each if [Date of Birth] < DateTime.Date(DateTime.LocalNow()) then "Past date" else if [Date of Birth] >= Date.StartOfQuarter(DateTime.Date(DateTime.LocalNow())) and [Date of Birth] <= Date.EndOfQuarter(DateTime.Date(DateTime.LocalNow())) then "Due in current quarter" else "Due in " & Text.Combine({Date.ToText([Date of Birth], "yy"), "Q", Text.From(Date.QuarterOfYear([Date of Birth]), "en-US")}), type text)
in
    #"Added Date Filter"

    The result:

    Best regards,
    Cidcley Barbosa

    • SudhanshuB's avatar
      SudhanshuB
      New Member
      2 years ago

      Thanks ! Its really complicated . Could you help me with a simplified DAX query . Let me reiterate my question again :
      I have a requirement where I need the data which are due in current quarter . For example ,

      AIIDApplication Name Next DR Scheduled
      12345X27th Jan 24
      43276Y02nd Mar 24
      86531Z8th Mar 24
      78856W28th Feb 24


      Here in the above table it shows the DR date for X is already completed . Now we are in month of February 24 and we have 3 more DRs to be performed . Hence, If someone asks how many DRs are due for current month , it should be 3 . So, I need to dynamically either have a date filter applied or add a measure to achieve . 

       

      Please suggest .

      • CidcleyBarbosa's avatar
        CidcleyBarbosa
        Advocate IV
        2 years ago

        You can add the  'Date Filter' column in a slicer, select 'Due in current quarter' and, in a visual, add a measure with a simple COUNT. 

    • SudhanshuB's avatar
      SudhanshuB
      New Member
      2 years ago

      Thanks ! Its really complicated . Could you help me with a simplified DAX query . Let me reiterate my question again :
      I have a requirement where I need the data which are due in current quarter . For example ,

      AIIDApplication Name Next DR Scheduled
      12345X27th Jan 24
      43276Y02nd Mar 24
      86531Z8th Mar 24
      78856W28th Feb 24


      Here in the above table it shows the DR date for X is already completed . Now we are in month of February 24 and we have 3 more DRs to be performed . Hence, If someone asks how many DRs are due for current month , it should be 3 . So, I need to dynamically either have a date filter applied or add a measure to achieve . 

       

      Please suggest .

  • 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