cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
giorajo
Helper I
Helper I

Always Displaying Same Value

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

 

Screen Shot 2020-08-27 at 3.25.54 AM.png

 

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.

 

1 ACCEPTED SOLUTION
dedelman_clng
Super User II
Super User II

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-08-26 15_12_42-scratch3 - Power BI Desktop.png

 

2020 (vs Dec 2019)

2020-08-26 15_12_53-scratch3 - Power BI Desktop.png

 

Hope this helps

David




Proud to be a Super User!




View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User III
Super User III

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
pranit828
Super User II
Super User II

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]




PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile
dedelman_clng
Super User II
Super User II

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-08-26 15_12_42-scratch3 - Power BI Desktop.png

 

2020 (vs Dec 2019)

2020-08-26 15_12_53-scratch3 - Power BI Desktop.png

 

Hope this helps

David




Proud to be a Super User!




View solution in original post

Thank you very much, @dedelman_clng !

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors