Substract value from different month based on several conditions

[cosminzanfir]

Hello,
Please help me find a solution to the following problem.

I need to calculate the difference between this month value vs previous month.

The problem is that the calculation needs to take in consideration several other columns and make the substractions based on these columns as well.

Below you can find the columns I am working with (In excel file for calculation purposes).

The column from wich i am substracting is Time Y.m. 

The value from second month of the same year is substracted from the previous month of the same year , where Employee ID-S- ,Employee ID-H-  ,Stage and Job Key is the same. If anything changes , then a the value should be separated from the calculation.

In the table below I have marked each step in column Nr of calcul for each step.

Column Key Sort is the key made from Year*100+Month and Employee ID-S- that i tought that it will help.

In column Time Y.m based on stage and Employee ID-S- is the calculation value made in excel by hand.

Spoiler

The way the data should be displayed to the user is shown in the screen below.

Could you please advice?

Thank you

[cosminzanfir]

Hello. 

So, the solution that actually worked was to create a summarize table containing all the unique key's.
in that table i've created for each record a MAX Date for employee, a MIN date for employee and found de difference between these 2 values.

[v-juanli-msft]

Hi @cosminzanfir 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[cosminzanfir]

Still working on the final form. No working solution was provided but found something that does the trick. Will post once finished

[amitchandak]

@cosminzanfir , time intelligence, and date calendar can help you to solve these. Example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))


last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

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/

If it is very specific to HR with start date end date, refer: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos.

[Greg_Deckler]

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