Forum Discussion

Rate's avatar
Rate
Icon for Helper III rankHelper III
6 years ago
Solved

Quotas, Cummulative Sum and Remains

Hello all,   I am struggling with some calculations regarding quotas for clients.   I have a table with a list of clients and a maximum amount (quota) they can benefit from discounts. They can bu...
Need Help
  • DataZoe's avatar
    DataZoe
    6 years ago

    Rate Oops! I am sorry, I was re-reading your post and realized I missed you wanted it to not take into account today's cumulative, but the earlier one. Try this measure to do that:

     

    Cumulative with Quota Measure =
VAR _c_today = CALCULATE(
    sum( 'Table'[Income] ),
    filter(
        ALLSELECTED( 'Table'[Date] ),
        ISONORAFTER( 'Table'[Date], max( 'Table'[Date] ), DESC )
    )
) 
VAR _c_earlier = CALCULATE(
    sum( 'Table'[Income] ),
    filter(
        ALLSELECTED( 'Table'[Date] ),
        ISONORAFTER( 'Table'[Date], max( 'Table'[Date] ) -1, DESC )
    )
) 
VAR _q = sum( TableQuota[Max Quota] ) 
VAR _d = _c_today - _q 

return if( _d > 0, _q - _c_earlier, _c_today )

     

     

DataZoe's avatar
DataZoe
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Rate Please try this measure:

 

Cumulative with Quota Measure =
VAR _c = CALCULATE(
    sum( 'Table'[Income] ),
    filter(
        ALLSELECTED( 'Table'[Date] ),
        ISONORAFTER( 'Table'[Date], max( 'Table'[Date] ), DESC )
    )
) 
VAR _q = sum( TableQuota[Max Quota] ) 
VAR _d = _c - _q 

return if( _d > 0, _d, _c )

 

I wasn't sure if you had a Date table set up or not, so I used the same table date. You can change that to date table date too.

  • DataZoe's avatar
    DataZoe
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Rate Oops! I am sorry, I was re-reading your post and realized I missed you wanted it to not take into account today's cumulative, but the earlier one. Try this measure to do that:

     

    Cumulative with Quota Measure =
VAR _c_today = CALCULATE(
    sum( 'Table'[Income] ),
    filter(
        ALLSELECTED( 'Table'[Date] ),
        ISONORAFTER( 'Table'[Date], max( 'Table'[Date] ), DESC )
    )
) 
VAR _c_earlier = CALCULATE(
    sum( 'Table'[Income] ),
    filter(
        ALLSELECTED( 'Table'[Date] ),
        ISONORAFTER( 'Table'[Date], max( 'Table'[Date] ) -1, DESC )
    )
) 
VAR _q = sum( TableQuota[Max Quota] ) 
VAR _d = _c_today - _q 

return if( _d > 0, _q - _c_earlier, _c_today )