Retrieve month when a measure value is bigger then another measure value

[Anonymous]

Hi all,

For a project I want to show the following. 

I have a table showing per month where we should have been (baseline %) and where we are (Actual %), these two values are calculated with measures. What I want to show is which month the Actual % complete is on the baseline %. In other words, which is the month the latest actual % is bigger than the Baseline %.

In the example table below, the latest actual % complete is 86% for May 2020 (latest update), and the 'first time' this is bigger than the baseline is in January 2020. So the desired outcome is January 2020. This i can use to give a delay indication (4 months), without the need to dive into the underlying schedule.

As both Baseline % and Actual % are measures, I would like to have a measure which is capable of achieving this. 

Year	Month	Baseline %	Actual %	Desired Outcome
2019	November	78	67
2019	December	83	73
2020	January	87	76
2020	February	91	82
2020	March	94	84
2020	April	96	85
2020	May	97	86	January 2020
2020	June	98

If anyone can help me, this would greatly help me. Thanky you  

[amitchandak]

@Anonymous , You need to have month year in your table

New Measure = maxx(filter(summarize(Table,Table[Month year],"_1",[Baseline %],"_2",[Actual %]),[_1]>[_2]),[Month year])

If you expect more than one month , plot this measure with Month Year, In such case you can use countx in place of maxx, if needed

[Anonymous]

Hi Amtichandak,

Thank you for your quick reply! 

I've used your suggestion and added a month year to my date table:

Using your measure :

ESValue = MAXX(filter(summarize('Date','Date'[Month-Year],"_1",[% Baseline Complete],"_2",[% Actual Complete]),[_1]>[_2]),'Date'[Month-Year]) i get the following result:

Month-Year	Baseline	Actual	Current result	Desired Result
11-2019	78	67	11-2019
12-2019	83	73
1-2020	87	76	1-2020
2-2020	91	82
3-2020	94	84	3-2020
4-2020	96	85	4-2020
5-2020	97	86	5-2020	1-2020
6-2020	98

So I can retrieve the date, but not the correct one. 

[amitchandak]

@Anonymous , Try like with month year in YYYYMM format

New column

Month-Year = format([Date],"YYYYMM")

Measure =
var _max =maxx(filter(summarize(Date,Date[Month-Year],"_1"[% Actual Complete]),not(isblank()),Date[Month-Year])
Var _min = minx(filter(summarize(Date,Date[Month-Year],"_1",[Baseline]),[_1]>=_max),[Month-year])
return
_min

if answer is correct get the month name in return