Forum Discussion

newpbiuser01's avatar
newpbiuser01
Helper 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.

     

     

     

     

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

    Thank you 🙂