topic Re: Total Sold Last 45 Days Since Last Sold Data per Country in DAX Commands and Tips

Total Sold Last 45 Days Since Last Sold Data per Country

esgaranel — Mon, 05 Dec 2022 20:31:49 GMT

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

Re: Total Sold Last 45 Days Since Last Sold Data per Country

tamerj1 — Mon, 05 Dec 2022 20:04:47 GMT

esgaranel

Do you have a date table? Can you provide more context about your data and data model?

Re: Total Sold Last 45 Days Since Last Sold Data per Country

esgaranel — Mon, 05 Dec 2022 20:32:40 GMT

OK, thanks. I forgot it.

LINK TO DOWNLOAD DATASET:

https://drive.google.com/file/d/19jZC7b8IAwlKHWeQmYzDzLmLzxbHXvHJ/view?usp=share_link

Re: Total Sold Last 45 Days Since Last Sold Data per Country

tamerj1 — Mon, 05 Dec 2022 21:11:02 GMT

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

Re: Total Sold Last 45 Days Since Last Sold Data per Country

esgaranel — Tue, 06 Dec 2022 09:36:23 GMT

Sorry, it is not correct. The result should be as indicated in the screenshots.

Re: Total Sold Last 45 Days Since Last Sold Data per Country

tamerj1 — Tue, 06 Dec 2022 10:18:31 GMT

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?

Re: Total Sold Last 45 Days Since Last Sold Data per Country

esgaranel — Tue, 06 Dec 2022 13:37:46 GMT

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.

Re: Total Sold Last 45 Days Since Last Sold Data per Country

tamerj1 — Thu, 08 Dec 2022 11:39:47 GMT

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?

Re: Total Sold Last 45 Days Since Last Sold Data per Country

esgaranel — Thu, 08 Dec 2022 17:40:45 GMT

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] )