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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.