Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Display First and Last date based on two week index

I currently have an index column on my Date table that counts the amount of pay periods passed since the first of the year. Each pay period is two weeks. So from 1/1/2020 to 1/14/2020 the payroll ind...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Please try to use the what-if parameter. You can interact with the variable as a slicer, and visualize and quantify different key values in your reports.

    Here's my solution.

    First, I created the Payroll Index from Jan20 on my own way.

     

    Then, create a what-if parameter on the Modeling tab.

    Set your own parameters according to your needs, here my maximum value is set to 26.

    You can get a slider and a calculated table.

    Create a relationship between two tables, then you can filter through the slider.

     

    Now you can create a measure to get the text "1/1/2020 - 1/14/2020" to be displayed when the slider is selected to 0.

     

     

    Measure = MIN('Calendar'[Date])&"-"&MAX('Calendar'[Date])

     

     

    Reference: Use what-if parameters to visualize variables - Power BI | Microsoft Docs

    You can check more details from my attachment.

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

artpil's avatar
artpil
Resolver II
4 years ago

Hi,

 

Here's example how it can be done based on solution from this  post

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdLLDcQgEATRXDjb0nxggFgs55+GF/uyQ13fqVrq6yomYqfoKVGOIuU+/qiTBmmCVEhKMpL/SDNVUiMFqZMGaYJs1VsmJRnJSZXUSEFa9Z5pkCbIhaQbLSUZaQ2qmSqpkYLUSYM0Qe+ZWiYlGclJldRIQVr1kWmQJug900ZKMpKTVn3P1EhB6qSxkX+f2Mg+uh8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Payroll index from Jan2020" = _t]),
    Custom1 = List.Buffer(List.Zip({Source[Date],Source[#"Payroll index from Jan2020"]})),
    Custom2 = Source,
    #"Added Custom" = Table.AddColumn(Custom2, "DateMin", each List.Min(List.Transform(List.Select(Custom1, (x)=>x{1}=[#"Payroll index from Jan2020"]), each _{0}))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "DateMax", each List.Max(List.Transform(List.Select(Custom1, (x)=>x{1}=[#"Payroll index from Jan2020"]), each _{0})))
in
    #"Added Custom1"

 

Second solution using grouping function

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdLLDcQgEATRXDjb0nxggFgs55+GF/uyQ13fqVrq6yomYqfoKVGOIuU+/qiTBmmCVEhKMpL/SDNVUiMFqZMGaYJs1VsmJRnJSZXUSEFa9Z5pkCbIhaQbLSUZaQ2qmSqpkYLUSYM0Qe+ZWiYlGclJldRIQVr1kWmQJug900ZKMpKTVn3P1EhB6qSxkX+f2Mg+uh8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Payroll index from Jan2020" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Payroll index from Jan2020"}, {{"DateMin", each List.Min([Date]), type nullable text}, {"DateMax", each List.Max([Date]), type nullable text}}),
    #"Merged Queries" = Table.NestedJoin(Source, {"Payroll index from Jan2020"}, #"Grouped Rows", {"Payroll index from Jan2020"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"DateMin", "DateMax"}, {"DateMin", "DateMax"})
in
    #"Expanded Grouped Rows"

Hope this will help.

Artur

Anonymous's avatar
Anonymous
Not applicable
4 years ago

This is very close. But how do I make the source my Date table rather than just the sample json above?