Forum Discussion

Keks's avatar
Keks
Frequent Visitor
2 years ago
Solved

Remove filter on min date and keep max date

Hi everybodu,

 

I have a measure 'Turnover' = calculate(sum(analytic_line[amount]))

and i would like to have a new measure which take into account the max date but not the min date the user has selected (filter = range of dates).

 

I really don't find a solution. I try this but with no success : 

VAR MaxDate = max('analytic_line'[date])
RETURN
CALCULATE([Turnover],REMOVEFILTERS('analytic_line'[date]),'analytic_line'[date]<=MaxDate)
 
Can someone please help me?
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thank you Idrissshatila and ajohnso2 

    Hi, Keks 

    Based on your description, I've created the following sample data:

    In order for the maximum date selected by the user to affect the calculation of the measure, the minimum date is not. We need to create a calculated table with the following DAX expression to use as a date slicer:

    Date = VALUES(analytic_line[date])

    Then change your DAX expression to:

    Measure = 
VAR _maxdate = MAX('Date'[date])
RETURN CALCULATE([Turnover], REMOVEFILTERS(analytic_line[date]),'analytic_line'[date]<=_maxdate)

    Here are the results:

    I've provided the PBIX file used this time below.

     

     

    Best Regards

    Jianpeng Li

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you Idrissshatila and ajohnso2 

    Hi, Keks 

    Based on your description, I've created the following sample data:

    In order for the maximum date selected by the user to affect the calculation of the measure, the minimum date is not. We need to create a calculated table with the following DAX expression to use as a date slicer:

    Date = VALUES(analytic_line[date])

    Then change your DAX expression to:

    Measure = 
VAR _maxdate = MAX('Date'[date])
RETURN CALCULATE([Turnover], REMOVEFILTERS(analytic_line[date]),'analytic_line'[date]<=_maxdate)

    Here are the results:

    I've provided the PBIX file used this time below.

     

     

    Best Regards

    Jianpeng Li

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

  • Idrissshatila's avatar
    Idrissshatila
    Icon for Super User rankSuper User
    2 years ago

    Hello Keks ,

     

    try this

    New Measure = 
VAR MaxDate = LASTDATE('analytic_line'[date])
RETURN
CALCULATE(
    [Turnover],
    'analytic_line'[date] <= MaxDate
)
    • Keks's avatar
      Keks
      Frequent Visitor
      2 years ago

      Thanks but it doesn't work 😞 

  • ajohnso2's avatar
    ajohnso2
    Icon for Solution Supplier rankSolution Supplier
    2 years ago
    New Measure = 
VAR MaxDate = CALCULATE(MAX('analytic_line'[date]), ALL('analytic_line'[date]))
RETURN
CALCULATE(
    [Turnover],
    'analytic_line'[date] <= MaxDate
)

    Instructions not clear but this should get the max avaliable date in your data.