Forum Discussion

iamhuz's avatar
iamhuz
Frequent Visitor
3 years ago
Solved

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 ch...
Need Help
Tips and Tricks
  • DOLEARY85's avatar
    DOLEARY85
    3 years ago

    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's avatar
DOLEARY85
Icon for Resident Rockstar rankResident Rockstar
3 years ago

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