Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, I need help to calculate the total sold the last 45 days by country from each date record. For example: Germany has a total from 12/03/2022 to 10/19/2022 (45 days) and another total from 11/13/2022 to 09/29/2022 (45 days). It is not so easy. Thanks.
LINK TO DOWNLOAD DATASET:
https://drive.google.com/file/d/19jZC7b8IAwlKHWeQmYzDzLmLzxbHXvHJ/view?usp=share_link
@esgaranel
Please refer to attached sample file with the solution
Last 45 Days Sales =
VAR LastSoldDate = [Last Sold Date]
RETURN
CALCULATE (
SUM ( 'DATASET'[Sales] ),
DATESINPERIOD ( CalendaryTable[Date], LastSoldDate, - 45, DAY )
)
Sorry, it is not correct. The result should be as indicated in the screenshots.
Hi @esgaranel
Actually I was confused with the negative sales values. Now I'm even more confused about the statement "Spain and Germany have two different total sales on two different dates". What does that even mean?!! Which two date? Based on what?
The exercise is about creating a range of dates that go from the date of eachtot record - 45 days and add the total sales obtained within that range.
For example: Germany, which appears twice in screenshot, would have a total sales for the range between dates 12/03/2022 and 10/19/2022 (12/03/2022 - 45 days) and another total sales for the range between dates 11/13/2022 and 09/29/2022 (11/13/2022 - 45 days).
In short, the objective is not to take the maximum date and subtract 45 days. The objective is to take each of the dates and subtract the 45 days to obtain the total sales for each range.
Do you have a date table? Can you provide more context about your data and data model?
OK, thanks. I forgot it.
LINK TO DOWNLOAD DATASET:
https://drive.google.com/file/d/19jZC7b8IAwlKHWeQmYzDzLmLzxbHXvHJ/view?usp=share_link
Hi @esgaranel
Apologies for the late resonse. Actually this is much more complex than you would think. The reason is that the slicing by column do not actually exist. However, I will try to do something, of course no guarantees.
I have adoubt regarding negative sales. For example if I want to calculate the very last date with sales I guess I need to consider the dates which have SUM ( DATASET[Sales] ) > 0 is that correct?
Also when you say: "For example: Germany has a total from 12/03/2022 to 10/19/2022 (45 days) and another total from 11/13/2022 to 09/29/2022 (45 days)" How has the 11/13/2022 date been selected? Based on what?
Hello, first of all thank you for the attention you are giving me.
1- Negative values are returns. You don't have to discriminate anything. SUM( DATASET[Sales] )
2-
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |