Forum Discussion
Sum Column but Skip First Month for Each Tenant
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
Hi, Anonymous , 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] ) )
3 Replies
- CNENFRNL
Community Champion
Hi, Anonymous , 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] ) ) - amitchandak
Super User
Anonymous , 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. - Ashish_Mathur
Super User