Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
Hi @Anonymous,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
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
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?
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
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |