Forum Discussion

patri0t82's avatar
patri0t82
Post Patron
4 years ago
Solved

VALUES function with DATEADD

Hello, can someone please help me adjust this DAX measure? Currently it's collecting all hours within the selected date range, I'd like to use DATEADD to select minus one month

 

YTD Hours Count =
CALCULATE (
SUM ( 'Hours - Tabular'[Value] ),
USERELATIONSHIP ( 'Recordable'[Operating Area], 'Hours - Tabular'[Operating Area] ),
VALUES(CalendarTable[Date])
)
 
I tried 
YTD Hours Count =
CALCULATE (
SUM ( 'Hours - Tabular'[Value] ),
USERELATIONSHIP ( 'Recordable'[Operating Area], 'Hours - Tabular'[Operating Area] ),
VALUES(DATEADD(CalendarTable[Date], -1, MONTH))
)
 
but it didn't like that.
 
Thank you!
Need Help
  • patri0t82's avatar
    patri0t82
    4 years ago

    This works!

    YTD Hours Count (Last Month) =
    CALCULATE(
    TOTALYTD (
    SUM ( 'Hours - Tabular'[Value] ),
    DATEADD( 'CalendarTable'[Date], -1, MONTH)),
    USERELATIONSHIP ( 'Recordable'[Operating Area], 'Hours - Tabular'[Operating Area] )
    ) + 0

3 Replies

  • patri0t82's avatar
    patri0t82
    Post Patron
    4 years ago

    I think I figured it out:

     

    YTD Hours Count (Last Month) =
    CALCULATE (
    SUM ( 'Hours - Tabular'[Value] ),
    USERELATIONSHIP ( 'Recordable'[Operating Area], 'Hours - Tabular'[Operating Area] ),
    VALUES(CalendarTable[Date]),
    DATEADD(CalendarTable[Date],-1,MONTH)
    )
  • patri0t82's avatar
    patri0t82
    Post Patron
    4 years ago

    Turns out my solution isn't quite working properly. Can anyone tell me a better way of writing it?

  • patri0t82's avatar
    patri0t82
    Post Patron
    4 years ago

    This works!

    YTD Hours Count (Last Month) =
    CALCULATE(
    TOTALYTD (
    SUM ( 'Hours - Tabular'[Value] ),
    DATEADD( 'CalendarTable'[Date], -1, MONTH)),
    USERELATIONSHIP ( 'Recordable'[Operating Area], 'Hours - Tabular'[Operating Area] )
    ) + 0