Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
esgaranel
Frequent Visitor

Total Sold Last 45 Days Since Last Sold Data per Country

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.

 

esgaranel_0-1670270375367.png

 

LINK TO DOWNLOAD DATASET:

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

8 REPLIES 8
tamerj1
Super User
Super User

@esgaranel 
Please refer to attached sample file with the solution

1.png

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.

 

Sin título_01.png

Sin título_2.png

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.

tamerj1
Super User
Super User

@esgaranel 

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

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-

Sin título_05.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors