Forum Discussion

hokiefan1113's avatar
hokiefan1113
Advocate I
8 years ago

Handling bi-weekly data

 

How can I pull in data on a bi-weekly basis? For example, I want to pull all data from a historical pay edit employee table from 7/15- 7/28. Once it reaches 7/29, I want data ONLY from 7/29 - 8/11, and same thing for once it reaches 8/12 (only want data from 8/12-8/25). Is there a way to write a query so that this can be performed? 

Need Help

2 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    8 years ago

    hokiefan1113,

     

    The requirement is not very clear. You may check if the DAX below helps.

    Table =
VAR d =
    TODAY ()
RETURN
    FILTER (
        'Table1',
        [Date]
            >= DATE ( YEAR ( d ) - 1, MONTH ( d ), DAY ( d ) )
            && [Date]
                <= DATE ( YEAR ( d ) - 1, MONTH ( d ), DAY ( d ) )
                    + 13
    )
    • hokiefan1113's avatar
      hokiefan1113
      Advocate I
      8 years ago

      v-chuncz-msft Sorry if I wasn't clear with my requirements, but I'm pulling the data from SQL Server. I don't want to hardcode the dates like I do in my existing statement, so I believe you understood what I was trying to get at. But my question is where do I use a DAX expression like the one you wrote in your post? Is that a query for an entire table? How would I integrate such a query with my existing sql statement below to pull the data:

       

      declare @start_date datetime

      declare @end_date datetime

       

      --Date/time when we want to extract from
      select @start_date = '2018-07-14 14:35'

      select @ened_date = '2018-07-28 14:35' 

       

      --Pull all histedits marked as Pay Adjust Paid
      select * From hist_edit
      where status_id = 6
      and insert_ts > @start_date

      and insert_ts < @end_date 
      order by hist_edit_id