Forum Discussion

PhoebeK's avatar
PhoebeK
New Member
4 years ago

Issues with EOMONTH

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?

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

     

     

    or

     

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

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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. 

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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

     

     
  • johnt75's avatar
    johnt75
    Super User
    4 years ago

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