Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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
Solved! Go to Solution.
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.
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 )
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
and it works in a table, but when i try to add it to the dax provided for YoY% it will not work.
please help
Hi,
thank you for your message.
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.
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.
Hi,
Thank you for your link, but I cannot accesss to it.
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.
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.
User | Count |
---|---|
13 | |
12 | |
8 | |
8 | |
6 |
User | Count |
---|---|
28 | |
19 | |
13 | |
11 | |
7 |