Forum Discussion

Talal141218's avatar
Talal141218
Icon for Helper III rankHelper III
3 years ago
Solved

Filter next two years based on Today

Hi Experts, 

I want to filter quantity in the sales orders in the next 2 years. I traid to create a mesure, but it's not work as below : 

CALCULATE([CM_SalesLine_SalesOrderLine_OrderQuantity], Fact_SalesOrderLine[ShippingDateRequested_NK] > TODAY() )
Farthermore, the date, that's i want to filtered  is a column in the tabel and not in the separate table. 
Please let me know if you need more information. 
Thanks a lot in advance 
  • ValtteriN's avatar
    ValtteriN
    3 years ago

    Hi,

    Here is one way to do this:

    example data:

    Here we will show values 1, 2 and 3 since 4 and 5 are outside the 2 year period wanted.

    In addition to the original table we will need a calendar table:

     



    Dax:

    Measure 2 =
    var _sdate = TODAY()
    var _edate = MAXX(CALCULATETABLE(DATEADD('Calendar'[Date],2,YEAR),FILTER('Calendar','Calendar'[Date]=TODAY())),[Date]) return
    CALCULATE(SUM('Table (4)'[Value]),ALL('Calendar'),DATESBETWEEN('Calendar'[Date],_sdate,_edate))

    End result:

     

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

2 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi,

    Here is one way to do this:

    example data:

    Here we will show values 1, 2 and 3 since 4 and 5 are outside the 2 year period wanted.

    In addition to the original table we will need a calendar table:

     



    Dax:

    Measure 2 =
    var _sdate = TODAY()
    var _edate = MAXX(CALCULATETABLE(DATEADD('Calendar'[Date],2,YEAR),FILTER('Calendar','Calendar'[Date]=TODAY())),[Date]) return
    CALCULATE(SUM('Table (4)'[Value]),ALL('Calendar'),DATESBETWEEN('Calendar'[Date],_sdate,_edate))

    End result:

     

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Your measure is on the right track, but you'll want to incorporate a filter for the next two years, and ensure that the measure syntax is correct.

    so maybe try 

    Next2YearsQty = 
CALCULATE(
    [CM_SalesLine_SalesOrderLine_OrderQuantity],
    FILTER(
        ALL(Fact_SalesOrderLine[ShippingDateRequested_NK]),
        Fact_SalesOrderLine[ShippingDateRequested_NK] > TODAY() &&
        Fact_SalesOrderLine[ShippingDateRequested_NK] <= DATEADD(TODAY(), 2, YEAR)
    )
)