Forum Discussion

Gilly66's avatar
Gilly66
Frequent Visitor
2 years ago
Solved

Dax

I was looking to write a dax expression that would result in me being able to see all expiring contracts in 18 months 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Gilly66 ,

    I assume you have the following data:

    Please create a new measure for visuals filter:

    Measure = 
VAR CurrentDate = TODAY()
VAR FutureDate = EDATE(CurrentDate, 18)
RETURN
IF(SELECTEDVALUE('Table'[End Date])>=CurrentDate &&SELECTEDVALUE('Table'[End Date])<=FutureDate,1)

    And apply it to the filter:

    If you want to get the number of expiring contracts, create another new measure:

    Amount = 
VAR CurrentDate = TODAY()
VAR FutureDate = EDATE(CurrentDate, 18)
RETURN
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALL('Table'),
        'Table'[End Date] >= CurrentDate && 'Table'[End Date] <= FutureDate
    )
)

    And the output:

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

Gilly66 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.