Dax formula disappears when refresh data

[TriBon]

Hi there! 

I am new to the forum and have a basic knowledge of pivot tables, so please bear with me if this is a silly q. 

I am working on a pivot table and I created some new DAX formulas to be able to calculate the daily average spend across multiple insertion orders. 

The idea is to be able to use this pivot table every day to check the daily average spend day by day. To do so, I need to refresh the pivot data source, which will be longer every day, obvioulsy (to get the data I download a report from a separate platform).

I tried to do so, by changing the data source and click refresh all in the pivot, but once I did it the three new formulas that I created disappeared. (please see screengrab below to understand better). 
I have been looking everywhere and reading lots of tips, but I haven't found the right answer. I feel like it might be an easy one, but I am stuck.

Anyone able to kindly help me, please?


Thanks a lot!

T 

[Stachu]

I assume for the refresh you have to edit the query is that correct? it's a bit annoying property of Excel, that after you edit a query all the DAX measure disappear

the solution is to create a measure specific table where you will store the measures, e.g. like this:

the other benefit is that if you hide all the columns in that table then measures will show on the top of the field list

in case you need to use calculated columns the only option is to change the query in a way that doesn't require changes with the data refresh, e.g. extracting from all files from a folder rather than single file, etc., or adding new columns in Power Query so that they're added with every refresh