Forum Discussion

Tihannah's avatar
Tihannah
Icon for Resolver II rankResolver II
2 years ago
Solved

Creating Pay Period Start and End Dates from Calendar Table?

I have my main calendar table but would like to create a slicer based on Pay Period Start and End dates. Specifically to show both as one column, i.e.  11/17/23 - 11/30/23 (which is an actual pay period date range) and can be put into a dropdown filter for selection. I've looked and looked, but can't seem to find an example of this in any previously posted questions. Any help would be much appreciated!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Tihannah 

    ThanksJamesFR06 and lbendlin, I have the following additions. Based on your description, I use the following sample data:

    I created a slicer with date and set the slicer style to between:

    I stitched together the maximum and minimum values in the slicer into a single line with the following DAX:

     

    MEASURE =
VAR _start =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _End =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
RETURN
    _start & "-" & _End

     

    Select different start and end dates through slicer for dynamic splicing:

     

     

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

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

     

5 Replies

  • JamesFR06's avatar
    JamesFR06
    Icon for Resolver IV rankResolver IV
    2 years ago

    HI

     

    I did it 2 times

    The first one with a filter on date on filter panel

    The second one, i have created a second date table ofr the required period and linked it to my date table

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      Are these pay periods depending on any user supplied filters?  If not then there is no point in trying to compute them over and over again.  Use an external reference table.

      • Tihannah's avatar
        Tihannah
        Icon for Resolver II rankResolver II
        2 years ago

        No. They will be the only filter on the dashboard.  JamesFR06  is there supposed to be an attachment or link to your post? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Tihannah 

    ThanksJamesFR06 and lbendlin, I have the following additions. Based on your description, I use the following sample data:

    I created a slicer with date and set the slicer style to between:

    I stitched together the maximum and minimum values in the slicer into a single line with the following DAX:

     

    MEASURE =
VAR _start =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _End =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
RETURN
    _start & "-" & _End

     

    Select different start and end dates through slicer for dynamic splicing:

     

     

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

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