Date in Range for specific criteria

[mbagwan]

I've a data for pay period (Bi weekly every Satuday) and the amount.
I would like to get the latest pay period in the Bar chart (with prior) on bi weekly basis (every Thursday)
For an example, let's say our next pay period is on 25/02/2023 (Saturday) but this data will come from payroll on Thursday 02/03/2023 and Thursday we have a pipeline which process the data and push it to SQL on 02/03/2023 and on the same day my report will refresh and I want to see all the pay period and amount on 02/03/2023.
Note that untill 02/03/2023 I don't want to see 25/02/2023 pay period data.

[v-zhangti · Accepted solution]

Hi, @mbagwan 

You can try the following methods.

Date = CALENDAR(DATE(2023,1,1),DATE(2023,3,31))

Measure:

Next Thursday = CALCULATE(MAX('Date'[Date]),FILTER(ALL('Date'),[Weekday]=4&&[Weeknum]=MAX('Date'[Weeknum])+1))

Date Slicer = CALENDAR(DATE(2023,1,1),DATE(2023,3,31))

Measure =
IF ( SELECTEDVALUE ( 'Date Slicer'[Date] ) >= [Next Thursday]
        && SELECTEDVALUE ( 'Date Slicer'[Date] ) >= SELECTEDVALUE ( 'Table'[Pay Period] ),
    1,
    0
)

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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