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
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?
If we have no other filters measure calculates result for the combination of name, manufacture, item, and bill date in the PivotTable row.
Variables quarterStart and quarterEnd depend only on current date.
Variable lastBillDate returns it independently of the date, we removed date filter by ALL ( Table1[Bill date] )
Variable currentAmount sums amount for the combination of field values in PivotTable rows. Actually it's the same if you drag Amount into Values of PivotTable.
Variable totalAmount do the same but ignores Bill date, i.e. total for all dates for the given combination of name, manufacture and item.
Finally we check if totalAmount is more than 3000 and Bill date is within current quarter. If yes, we return currentAmount for the Bill date, otherwise blank value. Rows with blanks are ignored in PivotTable.
As a comment, if we work with defined date, not TODAY(), bit more filtering will be required. No need in it if result is always for the current date.
- thanks for detailed explanation.