Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
Community Champion

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

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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

Thank you very much, @dedelman_clng !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.