Forum Discussion
Dynamic date sort for last n year
Hi,
Please find the attachment of sample data and do the needful.
I have two date fields in separation page . namely Date of Joining Field and Exit Date . I nee dthe logic like
Count of Employees are who are joined before “Date of Joining” and “Exit Date” greater than given “Date of Joining” Example: Start Date = 01/01/2019 and End Date = 03/01/2020 i.e., Separation = Count of Employees “Date of joining” before 01/01/2019 & Exit Date greater than 01/01/2019
For this I have used a DAX with the support of community as Last Period Employee = var _min_date = minx(all('Calendar'),'Calendar'[Date]) var _Expression=if(ISFILTERED('CALENDAR'[Month Year]),maxx('CALENDAR',ENDOFMONTH(DATEADD('CALENDAR'[Date],-1,MONTH))),maxx('CALENDAR',DATEADD('CALENDAR'[Date],-1,YEAR))) Return CALCULATE(COUNTx(FILTER(Separation,Separation[Date of joining]<=_Expression && Separation[Date of joining]>=_min_date && (ISBLANK(Separation[Exit Date]) || Separation[Exit Date]>_Expression)),(Separation[Employee ID])),CROSSFILTER(Separation[Date of joining],'CALENDAR'[Date],None)) |
and it is working good but it is showing for last one year from today and if i need to check for last 2 years i need to change -1 to -2 so on but i need the same logic dynamically whenever i sort date range and i taken a calendar table as below and given realtionship to Calendar [date] to separation [date of joining] and separation[exit date]
Please do the needful as i have struck in this logic since 3 days & i am trying to attach a dummy data but i didnt notice any option here can anyone suggest me.
3 Replies
Refer my blog on :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Refer to last period calculation and change last year to last to last year by using -2
Yes but i need it dynamically amitchandak because everytime i cannot change as it is difficult for end users once the report publish
ifi sort for last 1 year from today it is showing right value
if i sort for last 2 year it is showing same as last 1 year i need it dynamically without replacing -1 with -2
Please do the needful with some dax changes.
Thank you in advanceUse a what if measure in your formula to give Dax a control
