cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
carolinejesus
New Member

Trouble with Date Filter

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

1 ACCEPTED SOLUTION
littlemojopuppy
Super User I
Super User I

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

View solution in original post

3 REPLIES 3
littlemojopuppy
Super User I
Super User I

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

View solution in original post

carolinejesus
New Member

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
Super User I
Super User I

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.

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.