Percentage YOY calculation based on columns available in Table

[ramshoney1225]

HI I have to calcualte % values for each year with the combination of Company available in a Pivot table and that measure should calcuate dynamically when i pull in Pivot table.

Ex: for year 2019 ( apple ) =  (Apple 2019 Headcount ) - (Apple 2018 Headcount) / (Apple 2018 Headcount )

                                           =  ( 106700 - 105800 ) / 105800       --> Nearly 1% 

                   2018 ( apple ) =  (Apple 2018 Headcount ) - (Apple 2017 Headcount) / (Apple 2017 Headcount )

                                          =  (105800 - 106400) / 106400          --> Nearly  -1%

                  2017 ( apple ) =  (Apple 2017 Headcount ) - (Apple 2016 Headcount) / (Apple 2016 Headcount )

                   2016 ( apple) = ( Should be blank of not having 2015 Head count data ) 

Simillary, same calucation should happend for NOkia, Samsung.

How to do that dynamic calcuation with only one measure i need to create ?

Actaully i'm unabl to upload the PBIX file, didnt see option to upload but the data is this only .

Company	Year	Headcount
Apple	2019	106700
Apple	2018	105800
Apple	2017	106400
Apple	2016	102500
Samsung	2019	92005
Samsung	2018	103564
Samsung	2017	105870
Samsung	2016	104553
Nokia	2019	146768
Nokia	2018	150711
Nokia	2017	152000
Nokia	2016	155000

THanks,

Rams

[nandukrishnavs · Accepted solution]

@ramshoney1225 

Try this DAX measure

Percentage YOY =
VAR _currentYear =
    SELECTEDVALUE ( 'Table'[Year] )
VAR _company =
    SELECTEDVALUE ( 'Table'[Company] )
VAR _current =
    SUM ( 'Table'[Headcount] )
VAR _prior =
    SUMX (
        FILTER (
            ALL (
                'Table'[Company],
                'Table'[Headcount],
                'Table'[Year]
            ),
            ( 'Table'[Year] = _currentYear - 1 )
                && ( 'Table'[Company] = _company )
        ),
        'Table'[Headcount]
    )
VAR _yoy =
    DIVIDE (
        _current - _prior,
        _prior,
        BLANK ()
    )
RETURN
    _yoy

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[amitchandak]

@ramshoney1225 , if you do not have date, then make sure you year is in separate table and try

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year] -[Last Year]
Diff % = divide( [This Year] -[Last Year],[Last Year])
//if you have date with date calendar

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))


YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

[harshnathani · Accepted solution]

Hi @ramshoney1225 ,

You can use the below measure

Previous Year Headcount % =

Var _max = MAX ('Table'[Year])

Var _previousYear = CALCULATE( MAX('Table'[Year]), FILTER(ALL('Table'), 'Table'[Year] < _max && 'Table'[Company] = MAX('Table'[Company])))

Var _previousCompany = CALCULATE( MAX('Table'[Company]), FILTER(ALL('Table'), 'Table'[Year] < _max && 'Table'[Company] = MAX('Table'[Company])))

var _previousYearHeadcount = CALCULATE( MAX('Table'[Headcount]), FILTER(ALL('Table'), 'Table'[Year] = _previousYear && 'Table'[Company] = _previousCompany))

RETURN

DIVIDE(MAX('Table'[Headcount]) - _previousYearHeadcount,_previousYearHeadcount)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[ramshoney1225]

Hi @harshnathani 

I tried with this as well, but getting all 0.50 Valus 😞 

[harshnathani]

@ramshoney1225 ,

Please create a measure not a Calculated Column.

Regards,

Harsh Nathani

[ramshoney1225]

Hi @harshnathani , @nandukrishnavs 

Thanks for the help, the both measures works as expected shared by you 🙂 

As intial i got response from @nandukrishnavs  , will make as "accept solution" if it allows 2 then will make @harshnathani  as well.

Before closing this, how to get master in DAX 😞 , any helpful links will be highly appreciated.

Actually i'm sturggle lot to write a DAX 😞 😞 😞 

Thanks alot.

Hi @amitchandak 

Thanks for sharing more information if it was in differnt tables.

Thanks,

Rams

[nandukrishnavs]

@ramshoney1225 

Refer to this https://docs.microsoft.com/en-us/power-bi/guided-learning/introductiontodax

[nandukrishnavs · Accepted solution]

@ramshoney1225 

Try this DAX measure

Percentage YOY =
VAR _currentYear =
    SELECTEDVALUE ( 'Table'[Year] )
VAR _company =
    SELECTEDVALUE ( 'Table'[Company] )
VAR _current =
    SUM ( 'Table'[Headcount] )
VAR _prior =
    SUMX (
        FILTER (
            ALL (
                'Table'[Company],
                'Table'[Headcount],
                'Table'[Year]
            ),
            ( 'Table'[Year] = _currentYear - 1 )
                && ( 'Table'[Company] = _company )
        ),
        'Table'[Headcount]
    )
VAR _yoy =
    DIVIDE (
        _current - _prior,
        _prior,
        BLANK ()
    )
RETURN
    _yoy

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[ramshoney1225]

Hi @nandukrishnavs,,@amitchandak ,@harshnathani 

Thanks for the promt Help with multiple measures :), But in my case all are coming from one table so i tried what Nandu mentioned, but i'm getting nulls, may be i missed complete information what i need.

Below is the once which i have showing the screenshot,but i created in Excle, which i'm uanble to write in DAX

Which i want the same as below in Power BI

One with actual values table, and one with % values table ( not having any Actual values )

😞 this is what my second graph should show lik, but i created that in excel, 

In my case i want that should be written in Dax 😞 

Thanks,

Rams 

[nandukrishnavs]

@ramshoney1225  You have to create a DAX measure. I think you have created a calculated column.

I tried your sample data, it is working for me. You can find the snapshot in my original post.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[ramshoney1225]

Ya, I created calculated columns :|.

My assumption is wrong , i though that it will do row by row SUMX used and i created a calculated column.

Ok will create that in measure, let you know 🙂