Forum Discussion
Skilled Sharercreating a summury table from existing PBD table
I have created a report that summarize values for each storage location using data come from ERP every week.
Thanks to previous advices I have got in this forum it works properly.
But now the Company would like to store the summary data each week just in order to keep the trend under controll.
I have created a new table Dashboard Stock Values to record data only for test.
But I wonder how I could transfer automatically the data avoiding any manual data input 52 times each year.
Stock values are calculated using measures.
Any help wil be appreciated.
Thanks in advance.
Hi gpiero,
Based on your description, you want to get the weekly summary of your records, right?
You can refer to below formula to get weekly summary data(weeknum function):
Base table:
Measures:
WeekNumber = WEEKNUM(MAX('Table'[Date]))
Weekly Summary =
var currDate=MAX('Table'[Date])
var CurrWeekNo=WEEKNUM(MAX('Table'[Date]))
return
SUMX(FILTER(ALL('Table'),AND(WEEKNUM('Table'[Date])=CurrWeekNo,YEAR([Date])=YEAR(currDate))),'Table'[Amount])Regards,
Xiaoxin Sheng
4 Replies
Could you be a little more specific in what is the output exxpected and some sample data to work out a solution.
Cheers
CheenuSing
- gpiero
my goal would be fill tha table Dashboard Stock Values (1st pct) automatically each time (weekly) the table MB52 will be refreshed by data extracted from ERP each week.
The measures existing in the table MB52.... contanins the value to transfer in the Dashboard.
Then I will add a row each week and every chart, report or analisys wil be based on the Dashbord Stock Values.
Thanks
gpiero
Hi gpiero,
Based on your description, you want to get the weekly summary of your records, right?
You can refer to below formula to get weekly summary data(weeknum function):
Base table:
Measures:
WeekNumber = WEEKNUM(MAX('Table'[Date]))
Weekly Summary =
var currDate=MAX('Table'[Date])
var CurrWeekNo=WEEKNUM(MAX('Table'[Date]))
return
SUMX(FILTER(ALL('Table'),AND(WEEKNUM('Table'[Date])=CurrWeekNo,YEAR([Date])=YEAR(currDate))),'Table'[Amount])Regards,
Xiaoxin Sheng- gpiero
Anonymous
many thanks for your help.
I'll try to apply your advice, altough I have not understood how this function will add a new row each week in the table Dashboard Stock Values.
gpiero
