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 vlaues on 2019, 2020 and 2021 years each for only Jan to May on these years so I can compare it in a chart. It also should be fixed on these years no substracting them.

 

This's what I have but it shows blank data:

 

CALCULATE (
SUM ( 'AMS Flights Data'[Total Pax (Manifest)] ),
FILTER (
ALLSELECTED ( 'AMS Flights Data' ),
'AMS Flights Data'[Actual.1].[Year] = 2019
)
)
 

Please help

 

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.

     

4 Replies

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

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    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.