Apply a Measure as a Filter to Another Measure

[newpbiuser01]

Hello,

I have a table that has spend for each month in the last three years. I am trying to create a visual to compare the year to date spend for each year. So as an example, for the latest year I have data for, I have spend data for January (Month = 1), the visual would show spend for Month 1 for all the previous years. 

My table is as follows:

YearMonthSpend

2022	1	352
2022	2	185
2022	3	500
2022	4	429
2022	5	408
2022	6	325
2022	7	168
2022	8	104
2022	9	221
2022	10	322
2022	11	271
2022	12	162
2023	1	414
2023	2	281
2023	3	443
2023	4	170
2023	5	463
2023	6	232
2023	7	183
2023	8	294
2023	9	140
2023	10	330
2023	11	250
2023	12	316
2024	1	468

To get the latest month I have data for, I create the following measure: 

Last Month of Latest Year =
CALCULATE (MAX ( 'Table'[Month] ), FILTER ( 'Table', [Year] = MAX ( 'Table'[Year] ) ))

This measure gives me the following result: 

I then use this to calculate spend for all the years for this month: 

Spend for Latest Month =
CALCULATE (SUM ( 'Table'[Spend] ), FILTER ( 'Table', [Month] <= [Last Month of Latest Year] ))

 However, this gives me the following number which are clearly not filtering the previous years for just the latest month (1):

Now if I hardcode the number 1 in the Spend Measure, I get the right answer - which is the spend for the latest month of the latest year:

Spend for Latest Month_Month 1 = CALCULATE(SUM('Table'[Spend]), FILTER('Table',[Month] <= 1))

Why can I not use the Last Month of Latest Year measure to filter the data table? It should be passed as a number (1 in this case) to the Spend measure right? I'd appreciate any insights on what's going on here. 

[v-rongtiep-msft]

Hi @newpbiuser01 ,

I have created a simple sample, please refer to it to see if it helps you.

Create measures.

Converts numeric columns to date columns for comparison.

date =DATE(MAX('Table'[Year]),MAX('Table'[Month]),1)

 Compare all dates to get the most recent date.

maxmonth = 
var _1= MAXX(ALL('Table'),[date])
return
IF([date]=_1,MAX('Table'[Month]),BLANK())

 Get the result.

result = var _1= MAXX(ALL('Table'),[maxmonth])
return
IF(MAX('Table'[Month])=_1,MAX('Table'[Spend]),BLANK())

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[newpbiuser01]

Hi @v-rongtiep-msft ,

This works but only if I have a table or a bar chart where I have show each year.  I am trying to show the Year to Date difference Year over Year. 

How would I apply the year filter to say,

I added two measures in the pbix file you attached: 

1.  current year = CALCULATE([result], FILTER('Table', [Year] = MAX('Table'[Year])))

2. current year -1 = CALCULATE([result], FILTER('Table', [Year] = max('Table'[Year])-1))

3. Test = max('Table'[Year])-1

I used the Test measure to make sure I'm picking the right year (2023). The first measure works just fine, however the second one comes up blank: 

 (my apologies, I am unable to attach the pbix file)

How do I get the right answer in current year -1?

How can I get the spend to just the 1st month of year 2023 (the latest month I have 2024 data for)?

[v-rongtiep-msft · Accepted solution]

Hi @newpbiuser01 ，

You can also try the next formula if you have the current month's data each time.

Measure = var _1month=MONTH(TODAY())
return
IF(MAX('Table'[Month])=_1month,MAX('Table'[Spend]),BLANK())

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[newpbiuser01]

Thank you 🙂