cancel
Showing results 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

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.

9 REPLIES 9
Super User

not sure about what do want.

supposing you have a table like

try to plot a measure like:

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

it worked like:

Frequent Visitor

oK Guys,

I Have a table like this :

 Date Sales 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/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

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

Frequent Visitor

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

Super User

please provide some sample data and the desired output

Frequent Visitor

Good morning Fellas ,

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

thank you so much

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 !

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.
Frequent Visitor

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

Frequent Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors