Forum Discussion

Icon for Post Patron rank

Post Patron

9 years ago

Solved

Calendar calculate

Greetings,

Need help to calculate sum of a value from today till the end of month using a relationship with shipment date ( which is inactive). Suggestions?

TIA

bdymit
9 years ago
I am assuming that your comment regarding an inactive relationship to Shipment Date means you have a Calendar Table.

See if this works.

=
CALCULATE (
SUM ( Table1[Column1] ),
FILTER ( ALL ( Calendar ), Calendar[Date] >= TODAY () ),
FILTER ( Calendar, Calendar[Date] <= EOMONTH ( TODAY () ,0) ),
USERELATIONSHIP ( Table1[Shipping Date], Calendar[Date] )
)

or, a little cleaner

=
CALCULATE (
    SUM ( Table1[Column1] ),
    FILTER (
        ALL ( Calendar ),
        Calendar[Date] >= TODAY ()
            && Calendar[Date] <= EOMONTH ( TODAY (), 0 )
    ),
    USERELATIONSHIP ( Table1[Shipping Date], Calendar[Date] )
)

4 Replies

bdymit
Resolver II
9 years ago
I am assuming that your comment regarding an inactive relationship to Shipment Date means you have a Calendar Table.

See if this works.

=
CALCULATE (
SUM ( Table1[Column1] ),
FILTER ( ALL ( Calendar ), Calendar[Date] >= TODAY () ),
FILTER ( Calendar, Calendar[Date] <= EOMONTH ( TODAY () ,0) ),
USERELATIONSHIP ( Table1[Shipping Date], Calendar[Date] )
)

or, a little cleaner

=
CALCULATE (
    SUM ( Table1[Column1] ),
    FILTER (
        ALL ( Calendar ),
        Calendar[Date] >= TODAY ()
            && Calendar[Date] <= EOMONTH ( TODAY (), 0 )
    ),
    USERELATIONSHIP ( Table1[Shipping Date], Calendar[Date] )
)
- Fcoatis
  Post Patron
  9 years ago
  Thank you for your prompt answer but:
  
  Did not accept ENDOFMONTH( TODAY ( ) ). Apparently I cant nest the two functions.
  - bdymit
    Resolver II
    9 years ago
    My apologies, I should have said to use EOMONTH, not ENDOFMONTH
    
    see if EOMONTH(TODAY(),0) works
    
    I have edited my answer above.

Related content