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 ...
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.

     

     

     

     

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.

       

       

       

       

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

        Thank you 🙂