Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity 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]
        )
    )

  • 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.