Forum Discussion

chat_peters's avatar
chat_peters
Helper III
3 years ago
Solved

Total for Max Month

I have a power bi table visual that looks like this(link) 

When I try to calculate the Total Cost for Max available month, the calculation automatically reverts to the last month (YY-MMM) with data for current year (23-May). 

What I want to do is to return a 0 for the max YY-MMM which is Jul 2023 in this scenario.

 

 

 

Thank you so much for reading. I tried 

MAXX(FILTER(Calendar, Calendar[MMM-YY] = MAX(Calendar[MMM-YY])), [Total])

 

This still gave me 500 which the Max month with data. Thank you for reading. Can someone please help me?

Need Help
  • Legend_11's avatar
    Legend_11
    3 years ago
    Measure = CALCULATE(sum(TOTALTRY[Total]),filter('Date','Date'[Month]= MAX('Date'[Month])))

    Try this one, you need to do sum first then apply filter on month

    note:  if date is from same table

    Measure = CALCULATE(sum(TOTALTRY[Total]),filter('TOTALTRY','TOTALTRY'[Month]= MAX('TOTALTRY'[Month])))

3 Replies

  • Legend_11's avatar
    Legend_11
    Resolver I
    3 years ago
    Measure = CALCULATE(sum(TOTALTRY[Total]),filter('Date','Date'[Month]= MAX('Date'[Month])))

    Try this one, you need to do sum first then apply filter on month

    note:  if date is from same table

    Measure = CALCULATE(sum(TOTALTRY[Total]),filter('TOTALTRY','TOTALTRY'[Month]= MAX('TOTALTRY'[Month])))
    • chat_peters's avatar
      chat_peters
      Helper III
      3 years ago

      this is the one I used. I used the filter inside calculate. Thank you so much!

  • Dhairya's avatar
    Dhairya
    Solution Supplier
    3 years ago

    Hey chat_peters 

    Please follow the below steps to achieve your requirement.

    Step1: Create the following three measures

    Sum of Total = SUM(data_2[Total])
    Max date = MAX(data_2[Month (MMM-YY)]) 
    IsMaxDate = IF([Max date] = CALCULATE([Max date], ALLSELECTED(data_2[Month (MMM-YY)])),0,[Sum of Total])


    Step 2: Plot required columns and measures (Month (MMM-YY), Sum of Total, IsMaxDate) you will get the following output which is required 

     



    If this helps you then please mark my solutions as accepted so that others can find it quickly when they face a similar issue.Thank You!