cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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

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?

 

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors