Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX time intelligence question

Hi,

 

I have been troubled by this problem for a long time. 

 

So my data source is a data table from a MSSQL server and my data is updated in the real time.

 

Now I want to get my today's sales, so I create this measure to calculate today's sales:

Sales Today= SUM(fact_sales[dollar sales])

(Noticed that my sales data is based on item level)

 

Now in the report, I can filter out my today sales using relative filter => "in this day"

 

Every thing works perfect in power bi desktop. However, when I publish this report to Power BI services, the relative filter won't work for me. It will filter out my next days sales after 5:00 PM, I am EST time zone. I already know it because in power bi services, the time intelligence dax measure is using UTC time. 

 

Any solution to make my today sales always reporting today sales in Power BI services?

 

Thanks,

Need Help

7 Replies

  • Anand24's avatar
    Anand24
    Super User
    6 years ago

    Hi Anonymous ,

     

    On Power BI Service, the now() function will return the UTC time. I don't think we can set the time zone on Power BI Service. To get the correct local time, we can add time different in your DAX formula, or use Power Query to get the local time zone datetime.

     

    Please refer to below links and that should probably solve your concern:

    https://www.youtube.com/watch?v=2kmFfbOeFJg

    https://www.youtube.com/watch?v=M1zquwmpnZE 

     

    You can also view to the solution of below thread:

    https://community.powerbi.com/t5/Service/changing-timezone-of-powerbi-service/td-p/153663

     

    Refer below link:

    https://radacad.com/solving-dax-time-zone-issue-in-power-bi

     

    Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    As you can see, my local time is 8 hours ahead of UTC time, so when UTC time is 4 pm, my local time is already the next day.
    So I created a condition column as follows and add this column to the slicer.

     

    Best regards,
    Lionel Chen

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

  • nandic's avatar
    nandic
    Resident Rockstar
    6 years ago

    Anonymous ,
    Is there an option to update time zone on Power BI Service (so that it is compatible with your time zone on dekstop version)?

    Example: if you use Office365 there is option to change time zone. Office365 settings will have impact on Power BI Service as well.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi nandic  

       

      Thanks for the earliest solution you gaved. 

       

      Unfortunately, it doesn't solve my problem. You can take a look with GilbertQ solution to find it out why.

       

      I really appreciate your help.

       

      Thanks,

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    After I reviewed all answers in this post, I don't think I was very clear with my questions.

    My ideally solution was creating an EST timezone calendar table using DAX. 

    This is a common date table I used in my report:

    ''' DAX

    Calendar =
    ADDCOLUMNS (
    CALENDAR (DATE(2020,01,01)),DATE(2020,12,31)),
    "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
    "MDY",FORMAT([Date],"MM/DD/YYYY/dddd"),
    "YMD",FORMAT([Date],"YYYY/MM/DD"),
    "Year", YEAR ( [Date] ),
    "Monthnumber", FORMAT ( [Date], "MM" ),
    "ISO Week",WEEKNUM([Date],1),
    "YearMonthnumber", FORMAT ( [Date], "DDmmm" ),
    "DayMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
    "DayOfWeekNumber", WEEKDAY ( [Date] , 2),
    "DayOfWeek", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
    )

    ```

     

    Most answers in this post are solving this problem in the power query stage which lead me to the right direction. I have followed GilbertQ  BLOG to solved my question, but I would thank you all for the answers provided.

     

    Regards,