Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DSwezey
Helper III
Helper III

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 index would be 0. From 1/15/2020 - 1/28/2020 would be a payroll index of 1, etc..

I want to create a measure that will display the first and last date for each payroll index in a label. So if I selected Payroll index 0 in a slider I would want the text "1/1/2020 - 1/14/2020" to be displayed. 

 

Not quite sure how to accomplish this.

 

DSwezey_0-1656092109543.png

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @DSwezey ,

 

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.

vstephenmsft_6-1656398967130.png

 

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

vstephenmsft_1-1656398468894.png

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

vstephenmsft_2-1656398492337.png

You can get a slider and a calculated table.

vstephenmsft_3-1656398557220.png

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

vstephenmsft_4-1656398615363.png

vstephenmsft_8-1656399121761.png

 

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])

 

 

vstephenmsft_7-1656399094938.png

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.

 

View solution in original post

8 REPLIES 8
v-stephen-msft
Community Support
Community Support

Hi @DSwezey ,

 

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.

vstephenmsft_6-1656398967130.png

 

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

vstephenmsft_1-1656398468894.png

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

vstephenmsft_2-1656398492337.png

You can get a slider and a calculated table.

vstephenmsft_3-1656398557220.png

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

vstephenmsft_4-1656398615363.png

vstephenmsft_8-1656399121761.png

 

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])

 

 

vstephenmsft_7-1656399094938.png

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
Resolver II
Resolver II

You have 2 options.

1 - simple solution 

artpil_0-1656343013694.png

Select Source step in my query and replace all the text with name of your query (if you don't know how to do it you can select your query and right click it and choose Reference

artpil_1-1656343136263.png

pq will create reference to your query. Copy thos reference and paste to Source step).

2 - more complicated - Open your query in advanced editor and delete in statement and name of last step. Next paste the code below

,
    Custom1a = List.Buffer(List.Zip({Source[Date],Source[#"Payroll index from Jan2020"]})),
    Custom2a = Source,
    #"Added Customa" = Table.AddColumn(Custom2a, "DateMin", each List.Min(List.Transform(List.Select(Custom1a, (x)=>x{1}=[#"Payroll index from Jan2020"]), each _{0}))),
    #"Added Custom1a" = Table.AddColumn(#"Added Customa", "DateMax", each List.Max(List.Transform(List.Select(Custom1a, (x)=>x{1}=[#"Payroll index from Jan2020"]), each _{0})))
in
    #"Added Custom1a"

and replace words Source with the name of last step from your query.

Hope it helps.

Artur

The only problem with that is my "Date" table was created in DAX, thus has no table in power query.

You have new options:

1. Create date table in power query - heres the code to  create table from 01-06-2020 until current day

let
    Source = Table.FromList(List.Numbers(Number.From(#date(2020,1,6)),Number.From( Date.From( DateTime.LocalNow()))-Date.DayOfWeek( Date.From(DateTime.LocalNow()),Day.Monday)+7-Number.From(#date(2020,1,6))), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom2" = Table.AddColumn(Source, "Payroll index from Jan2020", each Number.IntegerDivide(([Column1]-43836),7)),
    #"Renamed Columns" = Table.RenameColumns( Table.TransformColumnTypes(#"Added Custom2",{{"Column1", type date}}),{{"Column1", "Date"}}),
    Custom1 = List.Buffer(List.Zip({#"Renamed Columns"[Date],#"Renamed Columns"[#"Payroll index from Jan2020"]})),
    Custom2 = #"Renamed Columns",
    #"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"

2. Post your issue on DAX commands and tips forum.

Artur

artpil
Resolver II
Resolver II

Hi,

Try Group by on payrol index column and create aggreagations as minimum and maximum of date. Be carefoul that if in each year first period number is equal 0 then you should add custom column with the year number and group on that column as well.

 

Hope this will help you to create your own solution.

 

Artur

Not sure how to go about creating groups within a date table. These are my only options. 

DSwezey_0-1656100178105.png

 

Note: The index starts on 1/1/2020 so anything before that will have a negative value. It accumulates year to year so at the end of the year it doesn't reset to 0.

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors