Forum Discussion

YavuzDuran's avatar
YavuzDuran
Icon for Helper III rankHelper III
4 years ago
Solved

In next n calender months (Moving average)

Hi All,

 

I am calculating a Sales Commission for a month, ie. October

For this calculation, I am looking for the Sales in the last 3 months (not including October) and the % of those Sales charged off in these last 4 months including October.

Each sale has a "Sales Contract Date" and a "Charge off Date" and I want to find those sales which have a "Charge off Date" in these  4 calendar month periods.

 

ie. 

Sales ContractDate        Charge Off Date      Is Charged Off in 4 months

08/15/2021                       10/1/2021                        1

07/28/2021                       10/29/2021                      1

08/25/2021                       11/02/2021                      0

08/25/2021                       10/29/2021                      1

09/28/2021                       11/01/2021                      0

 

Let's calculate October Commission. So I will be looking for the sales in either of July, August, or September, the Charged off Date should be in either of July, August, September, or October to get 1 for the field "Is Charged Off in 4 months"

 

Sales Contract Date and Charge Off Date fields are in the Same Sales Table.

 

Waiting for your help

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi YavuzDuran ,

     

    Try measure like the following:

    Is Charged Off in 4 months =
VAR _selectMonth =
    SELECTEDVALUE( YearMonth[yearmonth] )
VAR _SalesContractDate_StartDate =
    DATE( YEAR( _selectMonth ), MONTH( _selectMonth ) - 3, 1 )
VAR _SalesContractDate_EndDate = _selectMonth - 1
VAR _ChargeoffDate =
    DATE( YEAR( _selectMonth ), MONTH( _selectMonth ) + 1, 1 ) - 1
VAR _C_Sale =
    SELECTEDVALUE( 'Table'[Sales ContractDate] )
VAR _C_Charge =
    SELECTEDVALUE( 'Table'[Charge Off Date] )
RETURN
    IF(
        _C_Sale >= _SalesContractDate_StartDate
            && _C_Sale <= _SalesContractDate_EndDate
            && _C_Charge >= _SalesContractDate_StartDate
            && _C_Charge <= _ChargeoffDate,
        1,
        0
    )

    reslult:

     

    If you want a column:

    Is Charged Off in 4 months (column) =
VAR _selectMonth =
    DATE( 2021, 10, 1 ) // change the date you want to calculate.
VAR _SalesContractDate_StartDate =
    DATE( YEAR( _selectMonth ), MONTH( _selectMonth ) - 3, 1 )
VAR _SalesContractDate_EndDate = _selectMonth - 1
VAR _ChargeoffDate =
    DATE( YEAR( _selectMonth ), MONTH( _selectMonth ) + 1, 1 ) - 1
VAR _C_Sale = [Sales ContractDate]
VAR _C_Charge = [Charge Off Date]
RETURN
    IF(
        _C_Sale >= _SalesContractDate_StartDate
            && _C_Sale <= _SalesContractDate_EndDate
            && _C_Charge >= _SalesContractDate_StartDate
            && _C_Charge <= _ChargeoffDate,
        1,
        0
    )

     

    I put my pbix file in the end you can refer

     


    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    4 years ago

    YavuzDuran 
    First place you need to have the DateDim Table and relation ship with sales contract date
    then this formula will help you!
    if(chargedoffdate = 1, 
    averagex(datesinperiod(datesdim,lastdate(datesdate),-3,month),totalsales), Blank())

    • YavuzDuran's avatar
      YavuzDuran
      Icon for Helper III rankHelper III
      4 years ago

      Sorry, my bad VijayP I need to find if an account is 1 or 0 (Charged Off in 4 months) first. 

      I need this formula

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi YavuzDuran ,

         

        Try measure like the following:

        Is Charged Off in 4 months =
VAR _selectMonth =
    SELECTEDVALUE( YearMonth[yearmonth] )
VAR _SalesContractDate_StartDate =
    DATE( YEAR( _selectMonth ), MONTH( _selectMonth ) - 3, 1 )
VAR _SalesContractDate_EndDate = _selectMonth - 1
VAR _ChargeoffDate =
    DATE( YEAR( _selectMonth ), MONTH( _selectMonth ) + 1, 1 ) - 1
VAR _C_Sale =
    SELECTEDVALUE( 'Table'[Sales ContractDate] )
VAR _C_Charge =
    SELECTEDVALUE( 'Table'[Charge Off Date] )
RETURN
    IF(
        _C_Sale >= _SalesContractDate_StartDate
            && _C_Sale <= _SalesContractDate_EndDate
            && _C_Charge >= _SalesContractDate_StartDate
            && _C_Charge <= _ChargeoffDate,
        1,
        0
    )

        reslult:

         

        If you want a column:

        Is Charged Off in 4 months (column) =
VAR _selectMonth =
    DATE( 2021, 10, 1 ) // change the date you want to calculate.
VAR _SalesContractDate_StartDate =
    DATE( YEAR( _selectMonth ), MONTH( _selectMonth ) - 3, 1 )
VAR _SalesContractDate_EndDate = _selectMonth - 1
VAR _ChargeoffDate =
    DATE( YEAR( _selectMonth ), MONTH( _selectMonth ) + 1, 1 ) - 1
VAR _C_Sale = [Sales ContractDate]
VAR _C_Charge = [Charge Off Date]
RETURN
    IF(
        _C_Sale >= _SalesContractDate_StartDate
            && _C_Sale <= _SalesContractDate_EndDate
            && _C_Charge >= _SalesContractDate_StartDate
            && _C_Charge <= _ChargeoffDate,
        1,
        0
    )

         

        I put my pbix file in the end you can refer

         


        Best Regards

        Community Support Team _ chenwu zhu

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.