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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Chelito2019
Frequent Visitor

CREATE A MEAURE THAT RETURNS ONLY THE LAST 10 DAYS WITH SALES

Hi Guys,

 

I need to create a measure that returns only the last 10 days with sales, No exactly the last 10 days (No, From 20- 30), only the last 10 days with sales..e.g - Imagine we are in August, but the last 10 days with sales are (5,10,12,17,22,25,28,29,30), that´s the value the measure have to returns.

 

I have a table named "Sales" and a Date Table.

 

Thank you for your support.

9 REPLIES 9
FreemanZ
Super User
Super User

hi @Chelito2019 

not sure about what do want. 

supposing you have a table like

FreemanZ_0-1683079816441.png

try to plot a measure like:

Measure = 
SUMX(
    TOPN(
        10,
        Sales,
        Sales[Date]
    ),
    Sales[Sales]
)

it worked like:

FreemanZ_1-1683079872941.png

oK Guys,

 

I Have a table like this :

DateSales
1/3/2023                    -  
2/3/2023                    -  
3/3/2023       2,500.00
4/3/2023       3,500.00
5/3/2023       4,500.00
6/3/2023                    -  
7/3/2023                    -  
8/3/2023                    -  
9/3/2023                    -  
10/3/2023                    -  
11/3/2023                    -  
12/3/2023                    -  
13/3/2023                    -  
14/3/2023       4,500.00
15/3/2023     52,000.00
16/3/2023     35,000.00
17/3/2023     12,000.00
18/3/2023                    -  
19/3/2023                    -  
20/3/2023                    -  
21/3/2023                    -  
22/3/2023                    -  
23/3/2023                    -  
24/3/2023                    -  
25/3/2023                    -  
26/3/2023                    -  
27/3/2023     12,500.00
28/3/2023     11,500.00
29/3/2023     13,500.00
30/3/2023     14,500.00
31/3/2023     15,500.00

 

I need a measeure that only returns the last 10 days with sales, no the last 10 days (From 21-31), No, if you noticed the las 10 days with sales were as follow : 

5/3/202314/3/202315/3/202316/3/202317/3/202327/3/202328/3/202329/3/202330/3/202331/3/2023
          

 

I already tried with Window Function but it didn´t work.

 

Hopefully, now you have a whole picture about what i m looking for

 

Thank you much for your time

 

Regards

 

Silvio Roa

Sorry, I think my last post , don't show the result that i am looking for.

5/3/2023-14/3/2023 -15/3/2023- 16/3/2023-17/3/2023- 27/3/2023 -28/3/2023 -29/3/2023- 30/3/2023 31/3/2023

 

Hopefully, now the result looks like better !

 

Regards

wdx223_Daniel
Super User
Super User

please provide some sample data and the desired output

Chelito2019
Frequent Visitor

Good morning Fellas ,

 

I still looking for a solution , I already tested different solution but nothing works for me !

 

I need your help guys 

 

thank you so much 

Chelito2019
Frequent Visitor

Thank you so much for your time.! Dates is already in the filter context , I see that in the measure you use "Rel" , it means "Relation"? 

thank you so much , I will try it , and I get back with my feedback ! 

wdx223_Daniel
Super User
Super User

NewMeasure = CALCULATE(SUM(Sales[Amount]),WINDOW(-9,REL,0,REL,SUMMARIZE(ALL(Sales),Dates[Date]),ORDERBY(Dates[Date])))
if only there is dates in the filter context.

Sorry, but the measure doesn´t work.

 

I have a Date Table and Fact table "Sales", a column name !Sales Amount", a relationship between the Date Table & Sales table with the Primary Key "Dates".

 

But the measure says, summarize expect a column, the problem is with the Group By

Thank you so much for your time and your support, I appreciate it !

 

Regards

Daniel , 

I think -9 they "ll bring me the last 10 days !

But the idea is : E.g- During a month that have 30 days , the company only got sales in 10 different days , so the measure has to return only those days ... why ?Last  10 days with sales is # than The last 10 days   ? Did you see my point.  

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors