Previous Quarter records over a certain threshold

[thrillhouse]

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 out half this quarter and half next.  So, for example, if I am looking at a summary of Q4 2022 I want to have an additional column in the table that shows me what is payable from Q3 2022 (those transactions >100,000 divided by 2).  I am able to easily calculate records in the current selected quarter over 100,000 and split them in half, but I am having trouble writing a dax expression that correctly pulls those same transactions in the next quarter and split it in half to show what is payable from the previous quarter.  Any help is appreciated!

[v-yueyunzh-msft]

Hi, @thrillhouse 

Hello, according to your description, you want to calculate the value of the next quarter from the value of the previous quarter? And had a problem with dax. For context, it is difficult to give directly without some sample data.
If it is convenient for you, can you provide us with some sample data and your expected output data? (in tabular form)?

It will make the issure more clear and easy!

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]

Hi Aniya,

I am looking to carry forward an amount into the next quarter if it is above a certain threshold (for this example, lets say 100,000).  I have pasted a tables below to demonstrate what I am looking to do:

Let's say I am looking at a summary of Quarter 4 2022.  You will see that if the commission amount is over 100,000 I have divided the amount in half to be paid in this quarter.  What I am looking to do is carry the other half forward into the next Quarter. I am looking to create an additional column here for "Payable from Previous Quarter".  In this case,  it would include the second half of any >100,000 amounts from Q3.  

Q4 Summary:

Q4 Payable	Payable from Previous Period
405,404.5

Q4 Records Example:

SaleID	CommissionAmount	Date	Quarter	Payable this Quarter
6	95,000	10/1/2022	4	95,000.0
7	87,000	10/1/2022	4	87,000.0
8	138,700	10/1/2022	4	69,350.0
9	139,709	10/1/2022	4	69,854.5
10	84,200	10/1/2022	4	84,200.0

[v-yueyunzh-msft]

Hi , @thrillhouse 

Thanks for your quick response and your sample data! Here are the steps you can refer to :
This is my test data:

First , we need to click "New Column" and enter this to create a "Year_Quarter"column as a dimension.

Year_Quarter = YEAR([Date]) *100 +[Quarter]

[1]If you need to show the end result in a visual.

We need to click "New Measure" to create two measures:

Payable this Quarter = SUMX('Table', IF('Table'[CommissionAmount]>100000 , [CommissionAmount]/2 ,[CommissionAmount]))

Payable from Previous Period = var _cur_date  =MAX('Table'[Date])
var _cur_quarter  =MAX('Table'[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 put the fields on the visual and we can get this:

[2]If we want to add teh calculated columns in the table , we can also create two calculated columns in our table:

Payable this Quarter Column = SUMX(FILTER('Table','Table'[Year_Quarter]=EARLIER('Table'[Year_Quarter])), IF('Table'[CommissionAmount]>100000 , [CommissionAmount]/2 ,[CommissionAmount]))

Payable from Previous Period Column = var _cur_date  =[Date]
var _cur_quarter  =[Year_Quarter]
var _pre_year_quarter =MAXX( FILTER( 'Table'   ,'Table'[Year_Quarter]<_cur_quarter) , [Year_Quarter])
var _pre_table =  FILTER('Table' ,'Table'[Year_Quarter] =_pre_year_quarter)
return
SUMX(_pre_table , IF([CommissionAmount]>100000 , [CommissionAmount]/2 , 0))

Then we can get the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

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]

Hi Aniya,

Thank you for this!  This seems to work great, however, it doesn't work if I use a date filter to look at just a single quarter.  I'd like it to be able to show me the "Payable from Previous Period" column when I'm looking at a single quarter.

[v-yueyunzh-msft]

Hi , @thrillhouse 

Thanks for your quick response !Do you mean you want to filter by the slicer , if this , you just need to replce the ALLSELECTED() function with the ALL() function, like this:

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

Then we you select one year_quarter , the value show always.

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]

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]

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]

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:

Type	Payable This Quarter	Payable from Previous Quarter
A	251,854.50	60,000.00
B	153,550.00	69,600.00

[v-yueyunzh-msft · Accepted solution]

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