Second filter that would be between month beginning and ending

[Anonymous]

I have the following code, which sums up all my fees for my clients.

SumAmtVar = SUMX ( FILTER ( _billings, _billings[_tsg_clientid_value] = accounts[accountid] ), __billings[Amount] )

What I am needing is an additional filter that filters my transaction dates for the current month or a hard coded date range. What would the second filter look like? Any help or assistance is greatly appreciated.

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

Hi @Anonymous,

How does your data look like? It could be like below.

SumAmtVar =
SUMX (
    FILTER (
        _billings,
        _billings[_tsg_clientid_value] = accounts[accountid]
            && MONTH ( _billings[Date] ) = MONTH ( TODAY () )
    ),
    __billings[Amount]
)

or

SumAmtVar =
CALCULATE (
    SUMX (
        FILTER ( _billings, _billings[_tsg_clientid_value] = accounts[accountid] ),
        __billings[Amount]
    ),
    FILTER ( _billings, MONTH ( _billings[Date] ) = MONTH ( TODAY () ) )
)

Best Regards,

Dale

[Anonymous]

I am looking to do a filter between dates, for example August.  I need a filter the _billings[Date] as August 1, 2018 through August 31, 2018.  How do I ceate a between date range filter?

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Isn't MONTH ( _billings[Date] ) = MONTH ( TODAY () ) the August 1, 2018 to August 31, 2018? If you want a more dynamic method, it could be like below.

SumAmtVar =
CALCULATE (
    SUMX (
        FILTER ( _billings, _billings[_tsg_clientid_value] = accounts[accountid] ),
        __billings[Amount]
    ),
    FILTER (
        _billings,
        _billings[Date] >= DATE ( 2018, 8, 1 )
            && _billings[Date] <= DATE ( 2018, 8, 31 )
    )
)

Best Regards,

Dale