DAX time intelligence question

[Ccccche]

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,

[Ccccche]

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,

[GilbertQ]

Thanks for the update and details!

[v-lionel-msft]

Hi @Ccccche ,

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.

[Anand24]

Hi @Ccccche ,

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 !!!

[GilbertQ · Accepted solution]

Hi there

All the Power BI Servers have their dates set to UTC.

In my blog post below I detail why this is and how to make the required changes to your dataset

https://www.fourmoo.com/2017/10/03/power-bi-did-you-know-all-power-bi-services-servers-are-in-utc-no...

[nandic]

@Ccccche ,
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.

[Ccccche]

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,