Forum Discussion

RexaZii93's avatar
RexaZii93
Icon for Helper I rankHelper I
4 years ago
Solved

How to sum value for fixed years

  Hi I have a data table which has multiple years in it (2019 to 2022). I would like to have a measure that if I chose the slicer from Jan 2022 to May 2022 on my date filter it should sum the vlau...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi RexaZii93 ,

     

    You need create an independent calendar table first which has no relationship with the fact table. The same as the following screenshot.

    Then create a measure like this:

    Measure =
CALCULATE (
    SUM ( 'Fact table'[Values] ),
    FILTER (
        VALUES ( 'CALENDAR'[Date] ),
        MONTH ( [Date] )
            IN VALUES ( 'New calendar'[Month] )
                && DAY ( [Date] ) IN VALUES ( 'New calendar'[Day] )
    )
)

     

    Result:

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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

     

  • RexaZii93's avatar
    RexaZii93
    Icon for Helper I rankHelper I
    4 years ago

    Hello Tamer

    The post only shows how to calculate the values in the past three years (-3) so if I want a certain year lets say 2019 I would have to change -3 to -4 every year.

    what I would like is to calculate values on fixed year lets say 2019. Moreover, it only calculate the months I Pick on my callendar date, so if I chose Jan to July for 2022 it automatically do the same for 2019.

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      RexaZii93 

      Please provide sample file with dummy data to work with.