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...
Need Help
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