Forum Discussion

thrillhouse's avatar
thrillhouse
Icon for Helper I rankHelper I
3 years ago
Solved

Previous Quarter records over a certain threshold

I am looking to create field that utilizes records from the previous quarter only if the sale is over 100,000.  For context I am looking to split the current selected quarter's payment into 2 to pay ...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , thrillhouse 

    Thanks for your quick response and detailed description for your need.

    Here are the steps you can refer to .

    We need to update the two measures:

    Payable this Quarter = 
 var _cur_year_quarter = VALUES('Date'[Year_quarter])
 var _t = FILTER('Table' , 'Table'[Year_Quarter] in _cur_year_quarter)
 return
 SUMX(_t, IF('Table'[CommissionAmount]>100000 , [CommissionAmount]/2 ,[CommissionAmount]))
    Payable from Previous Period = var _cur_date  =MAX('Table'[Date])
var _cur_quarter  =MAX('Date'[Year_quarter])
var _type = MAX('Table'[Type])
var _pre_year_quarter =MAXX( FILTER(ALLSELECTED('Table') ,'Table'[Year_Quarter]<_cur_quarter && 'Table'[Type] = _type) , [Year_Quarter]) 
var _pre_table =  FILTER(ALLSELECTED('Table') ,'Table'[Year_Quarter] =_pre_year_quarter && 'Table'[Type] = _type ) 
return
 SUMX(_pre_table , IF([CommissionAmount]>100000 , [CommissionAmount]/2 , 0))

    If you want to get the Right total , you need to add a measure like this:

    Payable from Previous Period(Right Total) = SUMX( VALUES('Table'[Type]), [Payable from Previous Period])

    Then we can put the fields on the visual and we can meet your need:

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

thrillhouse's avatar
thrillhouse
Icon for Helper I rankHelper I
3 years ago

Appreciate your quick responses.  How would you account for an additional field in this calculation?  Lets say I want to filter on a specific person or category type. This formula looks to ignore all filters except the year/quarter filter.

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi , thrillhouse 

Thanks for your quick response , do you mean you want to be filtered by both date and other columns in your table?
If this , you need to click "New Table" to create a date table as a dimension:

Date = ADDCOLUMNS( CALENDAR( FIRSTDATE('Table'[Date]) , LASTDATE('Table'[Date])) , "Year_quarter" , YEAR([Date]) *100 +QUARTER([Date]))

And we do not need to create a relationship between two tables.

Then we need to update the old two measures:

Payable this Quarter = 
 var _cur_year_quarter = MAX('Date'[Year_quarter])
 var _t = FILTER('Table' , 'Table'[Year_Quarter] = _cur_year_quarter)
 return
 SUMX(_t, IF('Table'[CommissionAmount]>100000 , [CommissionAmount]/2 ,[CommissionAmount]))
Payable from Previous Period 2 = var _cur_date  =MAX('Table'[Date])
var _cur_quarter  =MAX('Date'[Year_quarter])
var _pre_year_quarter =MAXX( FILTER(ALLSELECTED('Table') ,'Table'[Year_Quarter]<_cur_quarter) , [Year_Quarter]) 
var _pre_table =  FILTER(ALLSELECTED('Table') ,'Table'[Year_Quarter] =_pre_year_quarter) 
return
SUMX(_pre_table , IF([CommissionAmount]>100000 , [CommissionAmount]/2 , 0))

Then we can meet your need , the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

  • thrillhouse's avatar
    thrillhouse
    Icon for Helper I rankHelper I
    3 years ago

    Hi Aniya,

     

    This is very helpful.  I realize I wasn't entirely clear in my description, but I'd like to be able to have these categories in the table and single select a quarter in the year and see the payable this quarter and payable from previous quarter.  In its current form, this repeats the same value for all categories.  It only functions as desired if we use "Type" as a filter rather than have it in

    the table view.  

     

    I'd like to see this table display as below:

    TypePayable This QuarterPayable from Previous Quarter
    A251,854.5060,000.00
    B153,550.0069,600.00
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi , thrillhouse 

    Thanks for your quick response and detailed description for your need.

    Here are the steps you can refer to .

    We need to update the two measures:

    Payable this Quarter = 
 var _cur_year_quarter = VALUES('Date'[Year_quarter])
 var _t = FILTER('Table' , 'Table'[Year_Quarter] in _cur_year_quarter)
 return
 SUMX(_t, IF('Table'[CommissionAmount]>100000 , [CommissionAmount]/2 ,[CommissionAmount]))
    Payable from Previous Period = var _cur_date  =MAX('Table'[Date])
var _cur_quarter  =MAX('Date'[Year_quarter])
var _type = MAX('Table'[Type])
var _pre_year_quarter =MAXX( FILTER(ALLSELECTED('Table') ,'Table'[Year_Quarter]<_cur_quarter && 'Table'[Type] = _type) , [Year_Quarter]) 
var _pre_table =  FILTER(ALLSELECTED('Table') ,'Table'[Year_Quarter] =_pre_year_quarter && 'Table'[Type] = _type ) 
return
 SUMX(_pre_table , IF([CommissionAmount]>100000 , [CommissionAmount]/2 , 0))

    If you want to get the Right total , you need to add a measure like this:

    Payable from Previous Period(Right Total) = SUMX( VALUES('Table'[Type]), [Payable from Previous Period])

    Then we can put the fields on the visual and we can meet your need:

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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