Forum Discussion
Apply Pivot Table on selected items with addition filter of billed in current quarter only
Let assume you your Excel version/platform supports data model. Creating PivotTable you need to add data to data model:
Right click on table name In PivotTable Fields pane and Add measure
Create DAX measure in opened window
Use this measure in PivotTable for aggregation
If with PivotTable - creating it we may add data to data model and use measures
Filtered Amount :=
VAR thisMonth =
INT ( ( MONTH ( TODAY () ) + 2 ) / 3 )
VAR quarterStart =
DATE ( YEAR ( TODAY () ), ( thisMonth - 1 ) * 3 + 1, 1 )
VAR quarterEnd =
EOMONTH ( DATE ( YEAR ( TODAY () ), ( thisMonth - 1 ) * 3 + 3, 1 ), 0 )
VAR lastBillDate =
CALCULATE ( MAX ( Table1[Bill date] ), ALL ( Table1[Bill date] ) )
VAR curentAmount =
SUM ( Table1[Amount] )
VAR totalAmount =
CALCULATE ( SUM ( Table1[Amount] ), ALL ( Table1[Bill date] ) )
RETURN
IF (
totalAmount >= 3000
&& lastBillDate >= quarterStart
&& lastBillDate <= quarterEnd,
curentAmount,
BLANK ()
)Result is
Thanks for ur detailed response.
1. I tried applying pivot table, but where & how to add filtered_amount column in it.
below is screenshot of pivot table i applied
2. Also if i direclty add bill_date in pivot table, it get broken into month, qtr & year.
what i do is first i change date format to number, apply pivot & then change format again to date. that way i can see full date dd-mm-yy in pivot.
Let assume you your Excel version/platform supports data model. Creating PivotTable you need to add data to data model:
Right click on table name In PivotTable Fields pane and Add measure
Create DAX measure in opened window
Use this measure in PivotTable for aggregation
SergeiBaklan thanks for your detailed post, been able to get the results
Now understanding this formula line by line
a) first line would get 3
b) second would be qtr start date
c) third would be qtr end date
how does pivot table only takes values which have this qtr date?
