topic Re: Issues with EOMONTH in DAX Commands and Tips

Issues with EOMONTH

PhoebeK — Thu, 03 Mar 2022 16:19:36 GMT

This formula:

Assigned last year =

CALCULATE([Case count],

USERELATIONSHIP('calendar'[Dates],'All cases combined'[Date assigned]),

DATESINPERIOD('All cases combined'[Date assigned],

EOMONTH(TODAY(), -1),

-12, MONTH)

is returning this visual:

This tweak to the formula:

Assigned last year =

CALCULATE([Case count],

USERELATIONSHIP('calendar'[Dates],'All cases combined'[Date assigned]),

DATESINPERIOD('All cases combined'[Date assigned],

EOMONTH(TODAY(), -2),

-12, MONTH)

returns this visual:

I know I have February in my data set. Why is this happening? How can I fix it?

Re: Issues with EOMONTH

amitchandak — Fri, 04 Mar 2022 03:13:53 GMT

PhoebeK , there nothing in screenshot one. Screenshot 2 is not having axis. What is missing.

eomonth(today(),-2) mean Jan -22 (we are in march now)

and rolling data for 12 month

also check

Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

Rolling 12 Sales =
var _max = eomonth( today() ,-1) // eomonth( today() ,-2)
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

Re: Issues with EOMONTH

tamerj1 — Fri, 04 Mar 2022 06:20:16 GMT

Hi PhoebeK

You are utilizing an inactive relationship. Once I strangely faced an issue where the solution was to CROSSFILTER - None the active relationship. Worth nothing to try.

Re: Issues with EOMONTH

v-yanjiang-msft — Wed, 09 Mar 2022 08:43:44 GMT

Hi PhoebeK ,

There is no problem with the formula itself, the specific reason needs to be combined with the relationship between your data.

If your problem hasn't been solved, could you please provide more about your sample? It will help find the problem faster.

Best Regards,
Community Support Team _ kalyj

Re: Issues with EOMONTH

johnt75 — Wed, 09 Mar 2022 10:09:45 GMT

Try changing the DATESINPERIOD function to use the 'calendar'[Dates] column instead of the column from your data table