Latest Month measure works quickly on desktop but not on service

[abloor]

Hi, just wondering if anyone can advise me why this issue is happening.

I have a report page that automatically ticks over at the beginning of the month to show last month's data.  E.g. on February 1st, I would expect it to show me January's data.  I have a filter on each visual ticked with 'yes' (see measure below.).

The issue I have found is in the service, the report doesn't tick over to the expected month until almost 2 days into the new month.  But it DOES tick over earlier than this in the desktop report.

Why would this be?

Measure:

LatestMonth =

VAR PreviousMonthYear = LOOKUPVALUE ('pbi vw_CalendarDate'[MonthYear], 'pbi vw_CalendarDate'[FullDate], EOMONTH( TODAY(), -1 ) )

RETURN

IF (

    'pbi vw_CalendarDate'[MonthYear] = PreviousMonthYear,

    "Yes",

    "No"

)

Thanks very much!

[abloor]

Thank you both @amitchandak  and @d_gosbell .  I will give these a go and see what happens when the new month ticks over 🙂

[v-jayw-msft]

Hi @abloor ,

Have your problem be solved? Please consider accept the answer as a solution if it worked.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[abloor]

Hi @v-jayw-msft , I did some testing on the first of the month and ran into a few small glitches with the suggestions offered (due to what I'd done).  I have made a few tweaks and will test it again on the 1st of April, then mark a solution once I know exactly what works.

Cheers,

Anna.

[d_gosbell]

I think this probably has to do with your use of the TODAY() function. This function will run relative to the date/time settings of the machine that is executing the code. So unless you are located in London I think this is probably a timezone issue because your local desktop will be running in a different timezone to the PowerBI service machines (which would most likely be set to a 0 UTC offset)

You could look at some of the options suggested by Reza here https://radacad.com/solving-dax-time-zone-issue-in-power-bi to work around this situation.

[amitchandak]

Try

LatestMonth =
VAR PreviousMonthYear = maxx(filter('pbi vw_CalendarDate','pbi vw_CalendarDate'[date]=date(year(today()),(month(today())-1),1)),'pbi vw_CalendarDate'[MonthYear])

RETURN
IF (
    'pbi vw_CalendarDate'[MonthYear] = PreviousMonthYear,
    "Yes",
    "No"
)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin