powerbidax

Fixed values but variable year


I have this two measures, the first one calculates the average sales of the full year and show it in my graph as a fixed value; the second one calculates and show the same but for the prior year.

Fixed= DIVIDE(
    CALCULATE(DIVIDE([Sales], 50), ALL('DataMain')),
    CALCULATE(DISTINCTCOUNT('Calendar'[Month]), ALLEXCEPT('Calendar', 'Calendar'[Year]))
)

FixedPriorYear = 
DIVIDE (
    CALCULATE ( 
        DIVIDE([Sales], 20), 
        ALL ( 'DataMain' ),
        PARALLELPERIOD('Calendar'[Date], -1, YEAR)
    ),
    CALCULATE ( 
        DISTINCTCOUNT ( 'Calendar'[Month]), 
        ALL ('Calendar'),
        PARALLELPERIOD('Calendar'[Date], -1, YEAR)
    )
)

The problem that i want to solve is that, the averge is correctly calculated and displayed, but when i change the the year using a slicer, the average is not recalculating.

Data example year 2023: | Month | Sales | | -------- | -------------- | | January | 100 | | February | 200 | | March | 300 | | April | 400 | | May | 500 | | June | 600 | | July | 700 | | August | 800 | | September| 900 | | October | 1000 | | November | 1100 | | December | 1200 |

data example

the sum of these values is 7800, the average is 650. With those measures i have the same result (650) to display it in each month. But when i change the year in my slicer, the result dont change.

Here´s a image to show what i have at the moment. I only need that when i change the year in my slicer, the average recalculate itself.

Expected visual result


Solution

  • This is the sample data I used for test.

    year month Sales
    2023 Jan 10
    2023 Feb 20
    2023 Mar 30
    2023 Apr 40
    2023 May 50
    2023 Jun 60
    2023 Jul 70
    2023 Aug 80
    2023 Sep 90
    2023 Oct 100
    2023 Nov 110
    2023 Dec 120
    2022 Jan 130
    2022 Feb 140
    2022 Mar 150
    2022 Apr 160
    2022 May 170
    2022 Jun 180
    2022 Jul 190
    2022 Aug 200
    2022 Sep 210
    2022 Oct 220
    2022 Nov 230
    2022 Dec 240

    you can create a measure

    MEASURE =
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER ( ALL ( 'Table' ), 'Table'[year] = MAX ( 'Table'[year] ) )
        ),
        CALCULATE (
            COUNT ( 'Table'[month] ),
            FILTER ( ALL ( 'Table' ), 'Table'[year] = MAX ( 'Table'[year] ) )
        )
    )
    

    enter image description here

    enter image description here