Hi, I am novice to Power BI and given 1 st project for attrition dashboard. i am currently stuck how to go about the this. i was not aware of DAX functions until i strated working on this project.
i have created 4 tables as below,
1-AttritionRaw - Fact Table where i am counting only actual Billable attritions this table has below fields Emp name, SOW Name, Last Working Date, Location, Designations etc
2-PipeLineAttritions - another Fact table, This table has got same columns as Attrition Raw to calculate future attrition for next 2 months
3-SOWBillableNo - This table has SOW names in column and Month (first day of every month 01-Jan-2016 and so on) and Billable Nos plotted below each SOW in rows including future 2 months tentative Billable Nos. I have pivoted this data in Power BI and shown monthly Billable Nos e.g. Month column 01-Jan-2016 (multiple dates in rows) and unique SOW in another column in each month and its Billable nos respectively
4- SOWMaster - This table has SOWName, Process Name, Process Group etc
5- have created 5 th table from Min(BillableMonth) and Max(BillableMonth) from SOWBillableNo table and required columns like YearNo, MM-YYYY, MonthYearShort, Quarter, QuarterYear etc
I wanted to show 12 month Rolling attrition% (Attrition Count last 12 months divided by Avg Billable HC for last 12 months) for last 12 months .i.e. if i select Apr 2019 it will show % for each month from Mar 2018 with same formula (i.e. Mar 2018 will have % of last 12 month attrition count divided by Average Billable no of last 12 months). Only difference calculating Attrition % is when we dont have 12 month data available it will calculate average of monthly (month attrition count divided by month Billable no) Attrition% x 12.
3 major issues are i am stuck at how to show last months in viz on selection of particular month. last 12 month Attrition Count, last 12 month Avg Billable No with Attrition% and % when we do not have 12 month trend mdata avilable.
