Subtracting row wise value from same column and showing the result as difference in new column

[aatish178]

Hi Team,

I have a table consisting of Fiscal year FY20 and FY21(both FYs starts from July) and for that I have count of employee ID column next to it...

Now I want to subtract count of empid for FY20 from FY21 without hardcoding the FYs in DAX. In future if FY22 comes then the subtraction should happen from FY21 to FY22..And so on

I have used EARLIER function but no luck yet. 

Please help me if something can be done in such case

[Anonymous]

Dear 
i need to find the difference between two value of different months, in my data e.g ID is common for ALL month, and against each ID have Months,  i need to calculate the difference between( Mar.23 - Apr.23) against the respective ID.

[quantumudit]

Hey @aatish178 

It would be great if you could provide a small sample dataset along with the expected solution dataset/image.

Best Regards,
Udit

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[aatish178]

Hi, 

Yes

------------------------------------

FY | Count of EmpID | Difference

-------------------------------------

FY20 | 100 | 0 (which is 100-100)

FY21 | 200 | -100 (100 - 200)

FY22 | 50 |  150 (200-50)

Hope this dataset helps

[quantumudit]

Is this the dataset you have? or,is this a sample of the solution you want? 

Best Regards,
Udit

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[aatish178]

FY and Count of Emp ID column I have as a dataset and I want to calculate difference from Count of Emp ID column and want to show it in separate column named "difference"

[quantumudit · Accepted solution]

Hi @aatish178 

You may use the following DAX formula to create a calculated column in the table to dynamically get the difference in employee count from the previous year

Difference = 
// Get Current and Previous Year
VAR _currentFY = CONVERT ( RIGHT ( DiffTable[FY], 2 ), INTEGER )
VAR _previousFY = _currentFY - 1 

// Get Employee ID count for current year
VAR _empCurrentFY =
    CALCULATE (
        SUM ( DiffTable[Count of EmpID] ),
        FILTER ( DiffTable, DiffTable[FY] = "FY" & _currentFY )
    ) 
    
// Get Employee ID count for previous year
VAR _empPreviousFY =
    CALCULATE (
        SUM ( DiffTable[Count of EmpID] ),
        FILTER ( DiffTable, DiffTable[FY] = "FY" & _previousFY )
    )
RETURN
    IF(
        ISBLANK(_empPreviousFY), 0,
        ABS ( _empPreviousFY - _empCurrentFY )
    ) // Absolute difference in employee count

Here is the screenshot of the solution

I hope this will help but, let me know if anything.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[aatish178]

Hi Udit...from screenshot it seems this solution works for future new FYs as well, if yes then that's what I was exactly looking for...also I have just started with power bi work before 3 weeks and here from the solution it seems the filter will not get applied to this result...is there a way to make slicer selection possible on this?

[quantumudit]

Hello @aatish178 

It's Udit actually and not Uday 😃
Yeah, the formula is future-proof... so, as the new FY comes, the formula should work.

If the solution worked, then, please mark it as a solution to help others find the solution quickly.

Best Regards,
Udit

Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[aatish178]

Sorry my bad...I had mistakely typed a wrong name, I had very hectic day mostly trying the different possibilities for the solution...but finally it worked because of you...thanks again...just a small doubt... I want the slicers to get applied on this solution...may I know what to do for it? Because I had seen the number is unchanged after slicer selection

[quantumudit]

Hi @aatish178 

If the field that you are choosing for a slicer is in the same table then, it should filter the table. If the slicer field belongs to some other field, then, you have to make sure that you have an active relationship between the tables 

[aatish178]

Hi Udit...the slicer selection is changing the resulting table but the calculated column value is still static. Is there a way to make the calculation which will respond to slicer selection. I tried using calculated measure but for both the FYs I am getting same no. Of employees., and the measure is not calculating the difference between adjacent FY Emp ID count.

[quantumudit]

Hi @aatish178 

The slicer should filter things properly. Please provide a sample table that you are using to filter the existing table... I am pretty sure, the issue is in relationships but, yeah I need to see the data model (maybe a screenshot will do)

[aatish178]

 see here is the output without applying any slicer and this output is correct.

[aatish178]

 here I have applied slicer selection of country and for that emp count is getting changed but Difference column value is still STATIC and here is the issue coming

[quantumudit]

There is something fundamentally incorrect in how you are doing it or how you have provided the problem...

It seems you have a relation between the table where you have FY and employee info and a table where you have country info.

I suspect the dataset you have provided initially is incomplete.

You need to understand, that Power BI runs on top of a data model and so as DAX. If you are unsure about how the relationship works and how a slicer filters the whole or, a part of the table, I urge you to provide the anonymized version of the actual datasets you are working with along with the relationships.

If you have a table with the following columns: FY, Count of Employee, and Difference (calculated column) and you are saying the country slicer is filtering the "Employee Count" and not the "Difference" then, this can't be true.

Well, another distant possibility could be, that you are unfamiliar with the Power BI terminologies and you are considering "Table Visual" or, "Matrix Visual" as a table. Well if this "distant possibility" is the case then, I would highly recommend getting familiar with the Power BI terminologies otherwise the results can be drastically different... because a "Table visual" works differently than a "table".

Best Regards,
Udit

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[aatish178]

The whole problem is with Count of Emp ID column...it is not a straight forward column used directly... 1stly in the table visual I am using emp Id as count of emp I'd to get count for each FY and on the top of that the difference is getting calculated. Hence problem is arising in the selection. I am using simple table visual and not the Matrix one

[quantumudit]

Now I got it..

You shouldn't call "Table Visual" as a "Table"... These are 2 different things and again you shouldn't ask Power BI questions by referring to visual.

The way you put your question is incorrect. You should have provided the data tables (In power bi terminology, when someone says "Table" we understand it as a data table and not the table visual) and the kind of relationship you have and then, you should have asked what kind of visual you want with what columns.

Since, you clarified it now. I would require a sample of all the tables (data tables not the table visual), also you need to mention the kind of relationship you have between the tables (if you have multiple data tables) and then, what the results you want ( In your case, I guess the result you want is a table visual with FY, employee count and different and a slicer to filter by country).

Kindly attach the datasets (or, send a link to download a sample PBIX) and your requirements with accurate terminologies.

Please go through this blog to understand how to ask your questions and what resources you need to provide to get your answers quickly and in one shot:

https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Udit

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[aatish178]

Thanks for clarifying... I will get back to you...as I have started few weeks back the PBI work hence there is confusion w.r.to terminologies 

[aatish178]

Hi, I am using single table only and all the calculations belongs to the same table. Here in front end when I added the calculated column in the table object then the value which came is not changing based on different selections made through different slicers. When I checked the issue , I got to know that only measures will respond to different selections and not the calculated column 

[aatish178]

I will look into it 🙂