Forum Discussion

hadrienpierre's avatar
hadrienpierre
New Member
2 years ago

How to avoid computed metrics in Service from being different than in Desktop

Hello everyone, 

 

I have a report that shows different results for the same metrics or added columns when being updated and refreshed from Desktop and from Service.
I've been made aware of the following caveats : 
- boolean filter being converted (not the case here)

- datetime difference / now(), today() evaluation in device time vs UTC server time

- model cache (emptied it)

- regional parameters of the model

 

My use case is flagging whether or not order lines. my dynamic data, were delivered on time, and then reporting about quality of service. So I need to compare datetime columns between themselves and to business rules expressed in time interval, e.g. if the order has an inbound reception date that is less than 2 business days from the outbound shipping date and the final delivery date is greater than the required date then not ok. 

 

So I created date tables in a semantic model to be able to perform operations on working day vs holidays, and I created my flagging columns in DAX. It gives the expected results on Desktop and when published from Desktop, but not from a refresh in Service, meaning the flag will change, without the underlying dates being displayed having changed.

Even working with a 10 row sample, for the life of me, I cannot see what causes that behavior, or what data might be wrongly converted. So what are the best practices to avoid that ? Did I go about solving the problem the wrong way around ? What to do in order to get dynamic variables that represent key business indicators without running head first into PowerBI architectural subtleties ?

Thanks for your help, 
 
 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, hadrienpierre 

    I am glad to help you.   

     

    Since you didn't give your specific dataset structure and the structure of your Date table, I made some assumptions of my own and I don't know if they match your dataset structure. 

    Here are my suggestions: 

    Create a Date table using New Table with fields like IsWeekend,IsHoliday: 

     

    Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2024, 12, 31 ) ),
    "Day", DAY ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Year", YEAR ( [Date] ),
    "DayOfWeek", WEEKDAY ( [Date], 2 ),
    "IsWeekend", IF ( WEEKDAY ( [Date], 2 ) >= 6, 1, 0 ),
    "IsHoliday", 0
)

     

    Then create a Calculated column to calculate Delivery Status: 

     

    Delivery Status = 
VAR InboundReceptionDate = Orders[Inbound Reception Date]
VAR OutboundShippingDate = Orders[Outbound Shipping Date]
VAR FinalDeliveryDate = Orders[Final Delivery Date]
VAR RequiredDate = Orders[Required Date]
VAR BusinessDaysBetween =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        'Date'[Date] >= OutboundShippingDate,
        'Date'[Date] <= InboundReceptionDate,
        'Date'[IsWeekend] = 0,
        'Date'[IsHoliday] = 0
    )
RETURN
    IF (
        BusinessDaysBetween < 2
            && FinalDeliveryDate > RequiredDate,
        "Not OK",
        "OK"
    )

     

    Also, as you said, you want to make sure that all date and time calculations use the same time zone (e.g., use UTC time), including when you refresh on Power BI Service. 

    I hope I've helped with the problem you're experiencing. 

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you. 

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.