Forum Discussion

hudsonvj's avatar
hudsonvj
Frequent Visitor
1 year ago

Unexpected result with time function

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)

 

 

General Comment
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      hudsonvj
      Frequent Visitor
      1 year ago

      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.