Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have raw data going back 6 years which is
1. date
2. Gross Profit (GP) figure at end of that day
3. Last month's cumulative GP at this date [this is because the GP figure for previous month changes during the next month due to credits/extra invoicing, so useful to know what it is on this date. e.g. for month end December, the most accurate GP month end figure is looking at December's figure at the end of January, which is what this column shows]
e.g.
11 Feb 2019 £644,000 £714,000
I've managed to achieve what I need, which is to work out for each date, how much extra GP was added between that date and the end of month, and then summarise this in a date hierarchy by month and date over the 6 years, e.g.
Month Day Avg GP on this date Avg addtl GP added by month end
February 11 £200,425 £238,432
What I now need to do is to use those 2 pieces of data together, i.e. I can get the current GP (i.e. yesterday's), in the example above: £644,000
How am I then able to get the average "Avg addtl GP added by month end" figure from an average date, e.g . the average of all the February 11th data, as the table has summarised above?
i.e. so I would present the data as
Current GP: £644,000
Avg addtl GP added by month end: £238,432
Estimated month end total: £882,432
@smsat wrote:
I have raw data going back 6 years which is
1. date
2. Gross Profit (GP) figure at end of that day
3. Last month's cumulative GP at this date [this is because the GP figure for previous month changes during the next month due to credits/extra invoicing, so useful to know what it is on this date. e.g. for month end December, the most accurate GP month end figure is looking at December's figure at the end of January, which is what this column shows]
e.g.
11 Feb 2019 £644,000 £714,000
I've managed to achieve what I need, which is to work out for each date, how much extra GP was added between that date and the end of month, and then summarise this in a date hierarchy by month and date over the 6 years, e.g.
Month Day Avg GP on this date Avg addtl GP added by month end
February 11 £200,425 £238,432
What I now need to do is to use those 2 pieces of data together, i.e. I can get the current GP (i.e. yesterday's), in the example above: £644,000
How am I then able to get the average "Avg addtl GP added by month end" figure from an average date, e.g . the average of all the February 11th data, as the table has summarised above?
i.e. so I would present the data as
Current GP: £644,000
Avg addtl GP added by month end: £238,432
Estimated month end total: £882,432
Could you share some sample data and clarify more details about the logic? For example, how do you get £200,425£238,432, Avg addtl GP from Current GP: £644,000?
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yuta-msft , just wondering if the info I provided below helped at all?
Would really appreciate your help if you can shed any light on this.
Thanks
Thanks for the reply @v-yuta-msft. I'll try and explain a bit more thoroughly. Below is an image of all data. There is a line entry for every day in the last 6 years, which shows
EOD GP = the cululative Gross Profit (GP) figure for all of that month, as at 2345 on that day, as supplied by our accounting system automatically into a file. ie on Feb 11, we'd made £254,634 of Gross Profit for the month so far
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |