Daily Transactions Bypassing Dates

[Anonymous]

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

[v-luwang-msft · Accepted solution]

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

[AlexisOlson]

Why 5 for each non-total row? You want to show the average sales?

[Anonymous]

@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).

[Anonymous]

@AlexisOlson The 5 represents the equal distributed total from the sample total of 35 across all of the dates. Ultimately, the business requirements is to look at sales over each quarter. I am replicating a report that was already created which had a calculated column dividing total sales / 90 days. 

Please let me know if this can help or is still confusing. 

[AlexisOlson]

OK. I'd suggest asking for what you're actually interested in achieving in your post. It's easy to remove all date filtering in a measure to return the total over all dates but that doesn't align with your specified output. I could write a measure that distributes the total over all dates but then I don't know if that's really what you want or if you actually want to distribute over each quarter separately or something else entirely.