Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Percentage YOY calculation based on columns available in Table

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 yea...
Need Help
  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    Anonymous 

     

    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     🙂

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    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)

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.