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.
Hello
I want to sum a total of real sales between dates contained on other table; however, with the formula below, I always get the total sales for all dates.
Table 1 | ||
Date of request | Stimated Volume Order for the month | Product |
15/04/2020 | 50 | A |
15/04/2020 | 100 | A |
15/04/2020 | 80 | B |
25/04/2020 | 100 | A |
25/04/2020 | 40 | B |
25/04/2020 | 50 | B |
10/05/2020 | 100 | A |
10/05/2020 | 200 | B |
15/05/2020 | 500 | A |
15/05/2020 | 300 | B |
Table 2 | ||
Date of sale | Real sales | Product |
01/04/2020 | 10 | A |
02/04/2020 | 3 | B |
03/04/2020 | 7 | A |
04/04/2020 | 13 | B |
05/04/2020 | 5 | A |
06/04/2020 | 4 | B |
07/04/2020 | 19 | A |
08/04/2020 | 13 | B |
09/04/2020 | 7 | A |
10/04/2020 | 18 | B |
11/04/2020 | 12 | A |
12/04/2020 | 11 | B |
13/04/2020 | 16 | A |
14/04/2020 | 6 | B |
15/04/2020 | 19 | A |
16/04/2020 | 8 | B |
17/04/2020 | 4 | A |
18/04/2020 | 19 | B |
19/04/2020 | 13 | A |
20/04/2020 | 7 | B |
21/04/2020 | 18 | A |
22/04/2020 | 12 | B |
23/04/2020 | 11 | A |
24/04/2020 | 16 | B |
25/04/2020 | 6 | A |
26/04/2020 | 19 | B |
27/04/2020 | 8 | A |
28/04/2020 | 7 | B |
29/04/2020 | 18 | A |
30/04/2020 | 12 | B |
01/05/2020 | 19 | A |
02/05/2020 | 8 | B |
03/05/2020 | 4 | A |
04/05/2020 | 19 | B |
05/05/2020 | 13 | A |
06/05/2020 | 7 | B |
07/05/2020 | 18 | B |
08/05/2020 | 12 | A |
09/05/2020 | 11 | B |
10/05/2020 | 4 | A |
11/05/2020 | 19 | B |
12/05/2020 | 13 | A |
13/05/2020 | 7 | B |
14/05/2020 | 18 | B |
Result desired | Sales as of Date of Request for April | Sales as of Date of Request for April | Sales as of Date of Request for May | Sales as of Date of Request for May |
Vol Sales by product | 15/04/2020 | 25/04/2020 | 10/05/2020 | 15/05/2020 |
A | 95 | 52 | 52 | 13 |
B | 68 | 62 | 63 | 44 |
Total | 163 | 114 | 115 | 57 |
ERROR | Sales as of Date of Request for April | Sales as of Date of Request for April | Sales as of Date of Request for May | Sales as of Date of Request for May |
Vol Sales by product | 15/04/2020 | 25/04/2020 | 10/05/2020 | 15/05/2020 |
A | 147 | 147 | 65 | 65 |
B | 130 | 130 | 107 | 107 |
Total | 277 | 277 | 172 | 172 |
Thanks in advance for your help
Solved! Go to Solution.
@Anonymous Guessing but seems like it should be something like:
measure =
var minDate = STARTOFMONTH(Table 1 [Date])
var maxDate = CALCULATE(Max(Table 1 [Date]),ALLSELECTED(Table 1 [Date]))
return
Calculate (
sum('Table 2'[Real Sales]),'Table 2'[Date]>=minDate && 'Table 2'[Date]<=maxDate))
I personally wouldn't use STARTOFMONTH as DATE(YEAR(MAX(['Table 1'[Date])),MONTH(MAX('Table 1'[Date])),1) is the same thing and I do not like TI functions at all.
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
Hi @Anonymous ,
Would you please explain more about your desired output? Please try the following measure:
measure = calculate(
var minDate = STARTOFMONTH(Table 1 [Date])
var maxDate = Max(Table 1 [Date])
return
Calculate (
sum(Table 2[Real Sales]),Table 1 [Date]>=minDate && Table 1 [Date]<=maxDate)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@Anonymous Guessing but seems like it should be something like:
measure =
var minDate = STARTOFMONTH(Table 1 [Date])
var maxDate = CALCULATE(Max(Table 1 [Date]),ALLSELECTED(Table 1 [Date]))
return
Calculate (
sum('Table 2'[Real Sales]),'Table 2'[Date]>=minDate && 'Table 2'[Date]<=maxDate))
I personally wouldn't use STARTOFMONTH as DATE(YEAR(MAX(['Table 1'[Date])),MONTH(MAX('Table 1'[Date])),1) is the same thing and I do not like TI functions at all.
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |