Sum Column but Skip First Month for Each Tenant

[kwats]

I have the following table below called Commercial Retail Sales and I'm trying to sum the 'Sales MTD' column after excluding the first month for each tenant. I tried the DAX formula below but it just sums up everything the same as Total Sales MTD = SUM( 'Commercial Retail Sales'[Sales MTD] ).

Total Sales MTD Without First Month =
VAR FirstMonth =
CALCULATE (
SUM( 'Commercial Retail Sales'[Sales MTD]),
DATESBETWEEN( 'Commercial Retail Sales'[Date], MIN( 'Commercial Retail Sales'[Date]) + 1, MAX( 'Commercial Retail Sales'[Date] ) )
)
RETURN

SUMX(
SUMMARIZE( 'Commercial Retail Sales', 'Commercial Retail Sales'[Tenant], "First Full Month", FirstMonth ),
[First Full Month]
)

I've been working on this for days and nights. Any help would be greatly appreciated! Thanks. Here's the data:

Date Tenant Sales MTD
5/31/2019 Tenant One $17,150
6/30/2019 Tenant One $96,261
7/31/2019 Tenant One $99,758
8/31/2019 Tenant One $88,102
9/30/2019 Tenant One $51,561
10/31/2019 Tenant One $47,480
11/30/2019 Tenant One $46,613
12/31/2019 Tenant One $57,197
1/31/2020 Tenant One $35,794
2/29/2020 Tenant One $43,086
3/31/2019 Tenant Two $5,077
4/30/2019 Tenant Two $91,166
5/31/2019 Tenant Two $93,417
6/30/2019 Tenant Two $89,959
7/31/2019 Tenant Two $90,011
8/31/2019 Tenant Two $92,519
9/30/2019 Tenant Two $85,027
10/31/2019 Tenant Two $89,492
11/30/2019 Tenant Two $94,165
12/31/2019 Tenant Two $112,937
1/31/2020 Tenant Two $101,008
2/29/2020 Tenant Two $94,330
5/31/2019 Tenant Three $11,752
6/30/2019 Tenant Three $29,620
7/31/2019 Tenant Three $34,719
8/31/2019 Tenant Three $38,345
9/30/2019 Tenant Three $37,270
10/31/2019 Tenant Three $32,755
11/30/2019 Tenant Three $39,677
12/31/2019 Tenant Three $32,127
1/31/2020 Tenant Three $31,074

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@kwats , Try like

calculate( sum(Sales MTD), filter( allselected(Table]), Table[Tenant] = max(Table[Tenant])  && Table[Date] >eomonth(min(Table[Date],0))))

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[CNENFRNL · Accepted solution]

Hi, @kwats , you may want to try this measure

Total Sales MTD Without First Month = 
SUMX (
    DISTINCT ( 'Commercial Retail Sales'[Tenant] ),
    SUMX (
        FILTER (
            DISTINCT ( 'Commercial Retail Sales'[Date] ),
            'Commercial Retail Sales'[Date]
                > CALCULATE (
                    MIN ( 'Commercial Retail Sales'[Date] ),
                    ALLEXCEPT ( 'Commercial Retail Sales', 'Commercial Retail Sales'[Tenant] )
                )
        ),
        [Total Sales MTD]
    )
)