Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
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,
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.
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 !!!
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.