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

## Calculate Last 10 days with specific condition

I have a Sales table like below, I want to create a measure for the last 10 days. Since I don't have data for 01-Apr-2023 so it should consider 23-Mar-23 as my 10th day.
Note that my dates can be changed so I want a dynamic measure

 Date Amount 20-Mar-23 10 21-Mar-23 3 22-Mar-23 6 23-Mar-23 5 02-Apr-23 6 03-Apr-23 4 04-Apr-23 2 05-Apr-23 3 06-Apr-23 10 07-Apr-23 5 08-Apr-23 8 09-Apr-23 7 10-Apr-23 6
1 ACCEPTED SOLUTION
Super User

Ah okay,

if you wrap the rank measure in a filter to calculate the amount based on the rank that is less than 10, that should work. try this:

Measure =
CALCULATE(sum('Table'[Amount]),filter('Table',RANKX (
FILTER ( ALLSELECTED ( 'Table' ),'Table'[Date]='Table'[Date]),
CALCULATE ( SELECTEDVALUE ( 'Table'[Date] ) ),
,
DESC,
DENSE
)<10))
8 REPLIES 8
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Thanks, DOLEARY85 for a quick response.
Please note that I'd like to use a card to show the total sales amount which is less then N
How to achieve that?

Super User

I'm assuming you want the N value to be selectable, if that's the case: could you just use a simple measure to calculate the sum of the amount for the card:

Measure = CALCULATE(sum('Table'[Amount]))

then use a slicer for less than or equal to using the amount column for the N value
Frequent Visitor

Hi DOLEARY85, I'd like to use it within a measure so no need to apply slicer or filter.

Super User
if you just want a measure then you could use the below i've set the N value to 90, however it's static so no user interaction would be possible. I'm not sure what your end goal is, if the user is to interact with this how will they be selecting the N value?

Measure = CALCULATE(sum('Table'[Amount]),filter('Table','Table'[Amount]< 90))
Frequent Visitor

Hi DOLEARY85, Basically I'd like to calculate the last 10 days' sales and show them on a card without any slicer or a filter.

Super User

Ah okay,

if you wrap the rank measure in a filter to calculate the amount based on the rank that is less than 10, that should work. try this:

Measure =
CALCULATE(sum('Table'[Amount]),filter('Table',RANKX (
FILTER ( ALLSELECTED ( 'Table' ),'Table'[Date]='Table'[Date]),
CALCULATE ( SELECTEDVALUE ( 'Table'[Date] ) ),
,
DESC,
DENSE
)<10))
Super User

Hi,

You could create a ranking that works dynamically and then use it on the filters on this visual in tha table

RANK =
RANKX (
FILTER ( ALLSELECTED ( 'Table' ),'Table'[Date]='Table'[Date]),
CALCULATE ( SELECTEDVALUE ( 'Table'[Date] ) ),
,
DESC,
DENSE
)

I had 6 dates and set the filter at 4 so you should just need to set yours to 10:

hope that helps

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