Forum Discussion

jppuam's avatar
jppuam
Helper V
2 years ago
Solved

calculating specific months

Hello,

i need to calculate the values from the current month in a metric, and also need to have to sum values of the previous 6 months.

having the filter, im doing the following below to calculated the valur from June.

When i need to calculate the last 6M, i need to have the values greater than 30 November 23 until May 24. But its not adding the values from 2023, even if i use ALL in the filter. can you understand why ?

thanks,

JR

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi jppuam ,

     

    My workaround changes dynamically based on the top value of the slicer, you can try it.

     

    Best Regards,

    Stephen Tao

     

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

     

     

7 Replies

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Hi jppuam ,

     

    Since you are using the ALLSELECTED the calculation is getting only the filtered values for this you need to use the ALL statement to get the full information and then apply the filter to the selected time frame.

    • jppuam's avatar
      jppuam
      Helper V
      2 years ago

      Hello Miguel,

      i've tested with ALL, and its not working.

      thansk for the respose.

      JR

  • jppuam's avatar
    jppuam
    Helper V
    2 years ago

    Hello,

    thanks for the response. I've tested with ALL, and it still doesnt work.

    I dont understand why....If i delete the data filter, the metrics work. or if i've put the biggining of the filter at the end of 2023, it works.....

    but for me it should work as im saying...

    JR

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi jppuam ,

       

      I think you should modify your measure as 

       

      Measure = 
var _date=CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table'))
var _end=EOMONTH(_date,-1)
var _start=EOMONTH(_date,-7)
return
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Date]>_start&&[Date]<=_end))

       

      Sample data:

      The measure should return the sum of the below even the slicer existing.

       

      Best Regards,

      Stephen Tao

       

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

       

       

      • jppuam's avatar
        jppuam
        Helper V
        2 years ago

        Thansk Stephen,

        i think it works.

        but just one thing that it is not ok, because i want to make the calculations basead on the top Month, the values that you have on the others month shouldn't be equal.

        for example, for this example, you are looking for the that from Dez23 - May24

        but the value for May24, should be the calculations between Nov23 and Apr24,

        and so one.

        JR