How do I insert calculated field to pivot table?

[JackDanIce]

Hi,

I have a column with calculated dates (column F) running next to a pivot table (columns G:H)

The number of rows expands as data is added and I would like to insert a calculated field with the formula as shown in column F
Field Row Labels represents weeks of the year
Field Sum of R are aggregate values per week from a named data table

Trade Journal.xlsm
	F	G	H
2	Date	Row Labels	Sum of R
3	07/09/2020	37	6
4	14/09/2020	38	-1
5	21/09/2020	39	5
6	28/09/2020	40	9
7	05/10/2020	41	5
8	12/10/2020	42	11
9	19/10/2020	43	14
10	26/10/2020	44	2
11	02/11/2020	45	3
12		Grand Total	54
Weekly Summary

Cell Formulas
Range	Formula
F3:F11	F3	=LET(Min_Year,YEAR(MIN(Trade_Data[[#All],[Date]])),DATE(Min_Year,1,-2)-WEEKDAY(DATE(Min_Year,1,3))+$G3*7)

How do I create this field in the pivot table itself so that on refresh, it'll auto calculate and update?

TIA,
Jack

 

[RoryA]

Calculated fields are value fields, not row/column fields. If you're using the data model, you can probably create a calculated column for that, but that would belong in the Power BI section where the DAX people live.

 

[JackDanIce]

Cool, yes, can see how a calculated column in the data table can achieve this. I'm still so basic with Power Query and barely touched Power BI!

Let me try that and confirm

.. that works, having slight formatting issue but can work with it, thanks @RoryA!