Unexpected result with time function

[hudsonvj]

Good morning, I'm having a problem finding where I'm going wrong in my model. I need to count the number of requests from the last date available in my database for each directorate. Those indicated with ARROW should not appear, as they are not issued on 08/20/2024, which is the last date available in my fWorkflow fact table. And the total quantity is correct. In short: those indicated with arrows should not appear.

1 - Measurements used

1.1 - Request Qty. = DISTINCTCOUNTNOBLANK(fWorkflow[Request])

1.2 - Measures I tested (both are bringing the same result)

RequestDiaEmitted = CALCULATE([QtyRequest.],DATESINPERIOD(fWorkflow[Request Date],MAX(fWorkflow[Request Date]),1,DAY))

RequestDiaEmitted = CALCULATE([QtyRequest.],DATESBETWEEN(dCalendario[Data],MAX(fWorkflow[Request Date]),MAX(fWorkflow[Request Date])))

Last date in fWorkflow

Relationships

Result that is giving (the directories indicated with the arrows should not appear, as they do not exist on 08/20/2024)

Structure of the dCadRequests dimension table (Field related to the fworkflow fact table - Request code -> Request) The board informed in the visual lines field I am informing from here. (Description of the Board)

[Anonymous]

Hi @hudsonvj ,

Based on the description, try to create a measure to identify the latest date.

LastRequestDate = MAX(fWorkflow[Request Date])

Then, using the following formula to calculate the requests.

RequestDiaEmitted = CALCULATE(
    [Request Qty.],
    FILTER(
        fWorkflow,
        fWorkflow[Request Date] = [LastRequestDate]
    )
)

Best Regards,

Wisdom Wu

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

[hudsonvj]

Good afternoon. Thanks for the feedback. Unfortunately it hasn't worked out yet. It gave the same result. Something in my model must be generating the incorrect result and I can't identify it.

In fact, I need to use a temporal function more, as I will need to perform four scenarios.Number of requests on: day, week, month, and year. That's why I was using 'DATESBETWEEN or DATESINPERIOD

Result with the structure you suggested.