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
iamhuz
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

DateAmount
20-Mar-2310
21-Mar-233
22-Mar-236
23-Mar-235
02-Apr-236
03-Apr-234
04-Apr-232
05-Apr-233
06-Apr-2310
07-Apr-235
08-Apr-238
09-Apr-237
10-Apr-236
1 ACCEPTED SOLUTION

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))

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
iamhuz
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?

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

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

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))

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.

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))
DOLEARY85
Super User
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:
 
DOLEARY85_0-1680385468694.png

 

hope that helps

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.