Trying to calculate time elapsed in a quarter

[DavidGM]

Hi All, I am trying to create a "Card" showing the time elapsed in a quarter. As mentioned below in the table start and end date of the quarter, its broken into 3week. After each 3rd week card should update as 25% time has elapsed, and so on to 100%.

Example: 30th nov start of the quarter and on 20th dec it should show as 25% time has elapsed, than after 3 weeks 50% and so on... and then again at the start of new quarter it should start with 25% to 100%. Desired output is %age of time Elapsed in Card visual. Thanks all 

Quarter	Start Date	End Date	Time Elpased
1	30 NOV	28 FEB
1.1 (3week)	30 Nov	20 dec	25%
1.2	21 dec	10 jan	50%
1.3	11 jan	31 jan	75%
1.4	1 feb	21 feb	100%
2 start of new of qtr	1 March	23 May
2.1	1 march	21 march	25%
2.2	22 march	11 apr	50%
2.3	12 apr	2 may	75%
2.4	3 may	23 may	100%

[djurecicK2]

No problem- maybe the approach could still work if you check for "-" instead of "." 

[djurecicK2]

Ok. I did try to confirm the quarter values based on your sample data😀

[DavidGM]

I know my bad... and for 1.1 in actual data FY22 P1-1, FY22 P1-2, FY22 P1-3, FY22 P1-4

So after P1-1 card value should show 25% time elapsed

P1-2 value changes to 50% in the visual.. .hope that helps thankyou

[djurecicK2 · Accepted solution]

Hi @DavidGM ,

 Assuming your quarter values have the same structure and only contain a "." if it is a partial quarter, you can use something like this as a calulcated column: (please modify to fit your table and field names)

Time Elapsed =

var hasdecimcal= FIND(".",DatesTable[Quarter],,0)

var rightvalue= RIGHT(DatesTable[Quarter],1)

return

IF(hasdecimcal >0,

 SWITCH(True(),

 rightvalue="1", "25%",

 rightvalue="2", "50%",

 rightvalue="3", "75%"),blank())

Please consider accepting as solution if this answers the question- thanks!

[DavidGM]

My actual qtr data are FY22 P1, FY22 P2 and I like to show as time progresses. Like currently we are at the start of quarter so on 20th dec it should show me 25%, 10 Jan 50%.. 

[TomasAndersson]

Below might work, partly depending on what context you want to use the measure in. __FiscalYearStartDate can be made more sofisticated. If you already have that date it in your date table you can use that instead. If you have start date of quarter, that would work too.

The theory is at least to to a MOD() calculation and depending on the result return the desired time elapsed in a SWITCH() statement. 

Time Elapsed = 
VAR __FiscalYearStartDate = if(month(max(Dates[Date]))=12,     
     Date(year(max(Dates[Date])),12,1),         //Or whatever the FY start date is
     Date(year(max(Dates[Date]))-1,12,1)
)
VAR __ThreeWeekPeriods = DIVIDE(DATEDIFF(__FiscalYearStartDate,MAX(Dates[Date]),WEEK),3) //Calculate the number of three week increments
VAR __QuarterIncrements = ROUNDDOWN(mod(__ThreeWeekPeriods,4),0) //Find out if it's the first, second, third or fourth three-week part of the quarter
return
SWITCH(__QuarterIncrements,0,0,1,0.25,2,0.5,3,0.75) //Return time elapsed depending on result

[djurecicK2]

Hi @DavidGM ,

 Is this is data you have currently? Everything except time elapsed column?

[DavidGM]

Yes, elapsed time is my output I want