How to calculate Total Sales showing only until the month before current month

[Bwendos_Den]

Hi All,

I have 3 different table CL (end date, contract id), sales actual ( #actual, dateid, contract id, doc date), and calendar (dateid, year, month, date).

They are related like the photo below.

What I want is that, when I select a month year from calendar, to have sales actual where the End date from CL is until the month before the selected month.

do you have any idea on how to solve it?

thank you very much for your help

[Bwendos_Den]

Hi @danextian thanks for your answer but it is not what what I am looking for, with that I still get the current month

[danextian]

Mmm. That is unexpected. Can you please post a workable sample data (not an image) or a sanitized copy of your pbix. You can post a link to a file in the cloud.

[danextian · Accepted solution]

Hi @Bwendos_Den ,

The sample measure below will calculate the sales up to before the earliest date of the selected month from the  calendar table.

CALCULATE (
    [sales measure],
    FILTER ( ALL ( 'calendar' ), 'calendar'[Date] < MIN ( 'calendar'[Date] ) )
)

Take note, the sales will be cumulative from the earlierst transaction date. If for just  the current year, try:

CALCULATE (
    [sales measure],
    FILTER (
        ALLEXCEPT ( 'calendar', 'calendar'[YEAR] ),
        'calendar'[Date] < MIN ( 'calendar'[Date] )
    )
)