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

8 Replies

  • 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

  • iamhuz's avatar
    iamhuz
    Frequent Visitor
    3 years ago

    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?

    • DOLEARY85's avatar
      DOLEARY85
      Icon for Resident Rockstar rankResident Rockstar
      3 years ago

      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
      • iamhuz's avatar
        iamhuz
        Frequent Visitor
        3 years ago

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