Forum Discussion

newpbiuser01's avatar
newpbiuser01
Icon for Helper V rankHelper V
2 years ago
Solved

Apply a Measure as a Filter to Another Measure

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

20221352
20222185
20223500
20224429
20225408
20226325
20227168
20228104
20229221
202210322
202211271
202212162
20231414
20232281
20233443
20234170
20235463
20236232
20237183
20238294
20239140
202310330
202311250
202312316
20241468

 

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. 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      newpbiuser01
      Icon for Helper V rankHelper V
      2 years ago

      Hi Anonymous ,

       

      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)?



       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.