Date Filter

[SudhanshuB]

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	1st Jan 24
Harry	4th Mar 24
Nick	29th 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 . 

[dufoq3]

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

[CidcleyBarbosa]

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]

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 ,

AIID	Application Name	Next DR Scheduled
12345	X	27th Jan 24
43276	Y	02nd Mar 24
86531	Z	8th Mar 24
78856	W	28th 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 .

[SudhanshuB]

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 ,

AIID	Application Name	Next DR Scheduled
12345	X	27th Jan 24
43276	Y	02nd Mar 24
86531	Z	8th Mar 24
78856	W	28th 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]

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.