Variance measure, how to make it work with date hierarchy e.g. Month / Quarter / Year
Hi, I have the following measure DCM Month to Month Variance % = DIVIDE( [DCM], CALCULATE( [DCM], DATEADD('Calendar'[Date], -1, MONTH ) ), 0 ) -1 This works great for showing the month to month variance of the DCM measure. What I want to do is to be able to drill down through Month > Quarter > Year and have the measure adjust to that time period accordingly. What I am trying to avoid is having 3 charts displaying this i.e. one of month variance, one for quarterly variance etc. Is this possible to do?
Counting rows before the selected period in slicer
Hi, My Fact table looks like below: Candidate Name Status Recruiter Full Name Job Ad Published date Timestamp Position Candidate 1 New PB 01/01/2020 02/01/2020 Support Worker Candidate 1 Phone screened KW 01/01/2020 05/01/2020 Support Worker Candidate 1 Interview complete PB 01/01/2020 24/01/2020 Support Worker Candidate 1 Offered role KW 01/01/2020 22/02/2020 Support Worker Candidate 2 New PB 01/01/2020 03/01/2020 Admin Officer Candidate 2 Phone screened KW 01/01/2020 05/01/2020 Admin Officer Candidate 2 Interview complete PB 01/01/2020 25/01/2020 Admin Officer Candidate 2 Offered role PB 01/01/2020 30/01/2020 Admin Officer Candidate 3 New PB 01/01/2020 03/01/2020 Finance Officer Candidate 3 Phone screened KW 01/01/2020 05/01/2020 Finance Officer Candidate 3 Interview complete PB 01/01/2020 25/01/2020 Finance Officer Candidate 3 Offered role KW 01/01/2020 30/01/2020 Finance Officer I need to calculate the following conversion rate for each position Formula is : Conv.rate per position =candidates offered role in current period/(interview completed in current period +previous periods) There is a slicer containing the Offered role date(Date hierarchy – Year, Month) Need the # of candidates that were Status=Interview completed before the slicer Period+Slicer period(Before selected offered Period-Month) Need the # of candidates that were status =Interview booked before the slicer period+Slicer period Need the # of candidates that were status=Phone Screened before the slicer period+Slicer period Need the # of candidates that were status=Offered role within the period selected in the slicer containing the Offered role date. Candidate name is considered unique. Appreciate all the help, Thanks,