Forum Discussion

giorajo's avatar
giorajo
Helper I
6 years ago
Solved

Always Displaying Same Value

I got a matrix displaying the name of clients in the rows and the sales per month on the column. 

 

 

I got a Year slicer for this matrix. I have a measure that gets the December sales of the previous year.

 

What I intend to do is add another column to display the sales for the month divided by the said December sales for the previous year. My problem is that the December sales is cannot be used monthly columns.

 

I hope my question makes sense. Thank you in advance.

 

  • dedelman_clng's avatar
    dedelman_clng
    6 years ago

    Hi giorajo 

     

    Try this code

     

    Total Sales = CALCULATE(SUM(Sales[Sales]))

Sales Rel to Prev Dec =
VAR __PrevYear =
    YEAR ( MAX ( DateTab[Date] ) ) - 1
VAR __PrevDec =
    CALCULATE (
        [Total Sales],
        DATESBETWEEN (
            DateTab[Date],
            DATE ( __PrevYear, 12, 1 ),
            DATE ( __PrevYear, 12, 31 )
        )
    )
RETURN
    CALCULATE ( DIVIDE ( [Total Sales], __PrevDec ), 0 )

     

    2019 (vs Dec 2018)

     

    2020 (vs Dec 2019)

     

    Hope this helps

    David

4 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    6 years ago

    Hi giorajo 

     

    Try this code

     

    Total Sales = CALCULATE(SUM(Sales[Sales]))

Sales Rel to Prev Dec =
VAR __PrevYear =
    YEAR ( MAX ( DateTab[Date] ) ) - 1
VAR __PrevDec =
    CALCULATE (
        [Total Sales],
        DATESBETWEEN (
            DateTab[Date],
            DATE ( __PrevYear, 12, 1 ),
            DATE ( __PrevYear, 12, 31 )
        )
    )
RETURN
    CALCULATE ( DIVIDE ( [Total Sales], __PrevDec ), 0 )

     

    2019 (vs Dec 2018)

     

    2020 (vs Dec 2019)

     

    Hope this helps

    David

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi giorajo 

    you have to create a measure for each month and user it in the table chart

    Jan_Dec = [Jan]/[Calc_December]

    Feb_Dec = [Feb]/[Calc_December]

    ...

    Jan_Dec = [Dec]/[Calc_December]