Forum Discussion
Daily Transactions Bypassing Dates
The goal is to calculate sales across all dates regardless if the sales occurred on that date or not. Currently, I am calculating sales that occurred on the date of the transaction (ex: "current_sales_output"). A new business requirement was added where sales are to be calculated over all dates (ex: "desired_sales_output"). Is there a way for me to calculate sales across all dates with the parameters not being before the date of the first sales transaction or beyond the date of the latest transaction? Below you will find the following: Current Sales Output Measure / Sample Data (with current output + desired output).
Your advice is greatly appreciated.
current_sales_output Measure
current_sales_output = SUM( Sales[Value] )
Sample Data (with current output + desired output)
| date | current_sales_output | desired_sales_output |
| Sunday, May 31, 2021 | 7 | 5 |
| Monday, June 1, 2021 | 0 | 5 |
| Tuesday, June 2, 2021 | 0 | 5 |
| Wednesday, June 3, 2021 | 14 | 5 |
| Thursday, June 4, 2021 | 7 | 5 |
| Friday, June 5, 2021 | 0 | 5 |
| Saturday, June 6, 2021 | 7 | 5 |
| TOTAL | 35 | 35 |
Hi Anonymous ，
IF you want to create a new coulumn to calculate output,use the following:
out1 = SUM('Table'[current_sales_output])/COUNTROWS('Table')And if you want to create a measure to calculate it ,use the following :
out2 = CALCULATE(SUM('Table'[current_sales_output])/COUNTROWS('Table'),ALL('Table'))Final output:
Wish it is helpful for you!
Best Regards
Lucien
6 Replies
- AlexisOlson
Super User
Why 5 for each non-total row? You want to show the average sales?
AlexisOlson I chose 5 for each non-total row because it was divisible by 7 (as in 7 days). I could have chosen a better number but the goal of my example was to show an example that would be simple and easy to understand. Apologies if it is confusing.
- AlexisOlson
I'm asking what it represents since 5 is clearly not the total across all dates that you are asking for in your original post but rather the total distributed equally across the dates (which you never mentioned as far as I can tell).
- v-luwang-msft
Community Support
Hi Anonymous ，
IF you want to create a new coulumn to calculate output,use the following:
out1 = SUM('Table'[current_sales_output])/COUNTROWS('Table')And if you want to create a measure to calculate it ,use the following :
out2 = CALCULATE(SUM('Table'[current_sales_output])/COUNTROWS('Table'),ALL('Table'))Final output:
Wish it is helpful for you!
Best Regards
Lucien
