Select max month bewteen actual and budget columns

[charlyrosero]

Hi!

Nowadays I need to always identify what is the last month of data for the current values. The following example shows the dataset that I have.

Month      Actual        Budget

January      100             90 

Febrary      200             120

March        60               220

June          180             150  --> i need to identify always this month

July                               100

August                          101

I appreciate any help

[v-lili6-msft · Accepted solution]

Hi@ charlyrosero

After my research , you can do these like below:

Step 1:

If column Month data type is not date ,you need to add a column that trans month in column Month into number like below:

Month No = IF(Table5[Month]="January",1,IF(Table5[Month]="Febrary",2,IF(Table5[Month]="March",3,IF(Table5[Month]="April",4,IF(Table5[Month]="May",5,IF(Table5[Month]="June",6,IF(Table5[Month]="July",7,
IF(Table5[Month]="August",8,IF(Table5[Month]="September",9,IF(Table5[Month]="October",10,IF(Table5[Month]="November",11,IF(Table5[Month]="December ",12)))))))))))) 

Step 2:

Add this Measure

Measure = CALCULATE(MAX(Table5[M]),FILTER(Table5,ISBLANK(Table5[ Actual ])=FALSE()&&ISBLANK(Table5[Budget])=FALSE() ))

Result:

Best Regards,

Lin