cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

Good afternoon,

I'm trying to figure out a dax to calculate the growth % year over year for each dept  with the sample query below.

I created a clustered column chart to show # of distinct position each year by department using a  query with similar headings below.

I would like to create a formula to show growth % year over year within each dept when hovering over certain year (sample graph i would like to show below).  Please help.  Thank you

1 ACCEPTED SOLUTION
Super User

Hi,

I think my sample model is simpler than yours.

In order to get previous year's numbers in your model, please try using relevant column from Dim Date table.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

7 REPLIES 7
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

``````Position # count: =
COUNTROWS( VALUES(Data[Position #]) )``````

``````Position # count YoY: =
VAR _currentyear = [Position # count:]
VAR _prevyear =
CALCULATE ( [Position # count:], FY[FY] = MAX ( FY[FY] ) - 1 )
RETURN
DIVIDE ( _currentyear - _prevyear, _prevyear )
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

Is there a way to count position # using distinct count? for example, if there's a repeated position number in the same fiscal year to only count it once. I tried this forumula

DistinctCountofPosition = DISTINCTCOUNT('Staffing Roster'[Position])

and it works in a table, but when i try to add it to the dax provided for YoY% it will not work.

Position # count YoY: =
VAR _currentyear = [DistinctCountofPosition]
VAR _prevyear =
CALCULATE ( [DistinctCountofPosition], FY[FY]= MAX ( FY[FY] - 1 )
RETURN
DIVIDE ( _currentyear - _prevyear, _prevyear )

Super User

Hi,

I am not sure but please check if you missed to insert  ")" like the below.

``````Position # count YoY: =
VAR _currentyear = [DistinctCountofPosition]
VAR _prevyear =
CALCULATE ( [DistinctCountofPosition], FY[FY] = MAX ( FY[FY] ) - 1 )
RETURN
DIVIDE ( _currentyear - _prevyear, _prevyear )
``````

Or, please share your sample pbix file's link, and then I can try to look into it to come up with a more accurate solution.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

Thank you so much for your help and patience.  here is the link to the sample data. I hope you are able to access it.

Super User

Hi,

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

Unfortunately, i am not able to share the file. So sorry. When adding the YoY to the value field table (see below) it returns 0 for all the  FY YoY.  . I hope this helps. Thank you so much again.

Super User

Hi,

I think my sample model is simpler than yours.

In order to get previous year's numbers in your model, please try using relevant column from Dim Date table.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors