Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculate Month Diff between selected date and min date

Hi, I would like to calculate the month difference between the selected date and min available date.

For example, the min date of this dataset is 2023-01, so MonthDiff is the expected result. How to create MonthDiff measure?

MonthMonthDiff
2023-011
2023-022
2023-033
2023-044
2023-055

 

 

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi Anonymous 
    You can use this type of formula modified by your parameters:

    month_diff =
    var selected_date = SELECTEDVALUE('Calendar'[Date])
    var min_order_date = CALCULATE(min('orders'[Order Date]),ALL('Calendar'))
    RETURN
    DATEDIFF(min_order_date,selected_date,MONTH)

    pbix is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

     

2 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi Anonymous 
    You can use this type of formula modified by your parameters:

    month_diff =
    var selected_date = SELECTEDVALUE('Calendar'[Date])
    var min_order_date = CALCULATE(min('orders'[Order Date]),ALL('Calendar'))
    RETURN
    DATEDIFF(min_order_date,selected_date,MONTH)

    pbix is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Is there a relationship between the DATES table and the Inventory table?  If so, the problem is likely to be the filtering context being applied.

    You could get around this problem using variables like in the DATEDIFF example linked below and using a CALCULATE statement and an ALL(DATES) i.e. var EndDate =  CALCULATE(MAX(Inventory[Date]), ALL(DATES))
    DATEDIFF function (DAX) - DAX | Microsoft Learn