Trouble with Date Filter

[carolinejesus]

Hi everyone

I'm having a trouble with a Date Segmentation in my table.

I needed to build a report that I would be able to filter Date Period and compare my data among them, so I made 3 calendars (with CALENDARAUTO() DAX formula).

After that I create a non-active relashionship with my Sales Sum Measure an then I used the DAX formula USERELASHIONSHIP() to active the relashionship made before and aplicated this measure in a table.

The problem is, when I filter the período between 11/01/20 and 11/01/20, there is no information and I realized that all the information of this Day are coming when I filter 10/31/20 (onde day before).

Have you ever been in an issue like that? Can you help me with it?

Thank you so much!!

[littlemojopuppy · Accepted solution]

The Power BI service runs on UTC time while Desktop is using the time zone of where you're located.  I've had to deal with this in the past reporting measures "for today" (users, etc.) which always reported incorrectly after midnight UTC.  How I dealt with it was essentially as follows inside the measure (sorry I don't have access to the DAX anymore)

1. Variable for UTCTODAY()
2. Variable for where you're located UTCNOW() - (NN/24) with NN being the number of time zones you are ahead of or behind UTC time
3. Compare the two and if the dates are the same handle one way.  If different handle appropriately.

Hope this helps!  😁

[carolinejesus]

Hi!! Thank you so much for your help

I think that I found the problem, It's a time zone problem!

When I post the report on Power BI Service and use the filter for Nov 1st the information there is corect, but on Power BI Desktop the information remains incorrect.

If you know how can I fix it on Power BI Desktop too, would you help me?

[littlemojopuppy]

Hi!  If I'm understanding you correctly, you have three date tables?  You should only need one (and mark it as a date table).  I would expect that you found that one measure was filtering but the other two were not?  Create active/inactive relationships between the date table and each of the date fields.  In the measures make sure you use the CALCULATE statement and include USERELATIONSHIP indicating which of the three relationships to use.  Would think you would be fine then.

If you'd like to provide some sample data I could play with it.