Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
DavidGM
Helper I
Helper I

Trying to calculate time elapsed in a quarter

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 

QuarterStart DateEnd DateTime Elpased 
130 NOV28 FEB 
1.1 (3week)30 Nov20 dec25%
1.221 dec 10 jan50%
1.311 jan31 jan75%
1.41 feb21 feb100%
2 start of new of qtr1 March23 May 
2.11 march21 march25%
2.222 march11 apr50%
2.312 apr2 may75%
2.43 may23 may100%
1 ACCEPTED SOLUTION
djurecicK2
Super User
Super User

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())
 
djurecicK2_0-1669758237399.png

 

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

 

View solution in original post

8 REPLIES 8
djurecicK2
Super User
Super User

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

djurecicK2
Super User
Super User

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

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
Super User
Super User

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())
 
djurecicK2_0-1669758237399.png

 

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

 

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
Solution Sage
Solution Sage

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
Super User
Super User

Hi @DavidGM ,

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

djurecicK2_0-1669755541427.png

 

Yes, elapsed time is my output I want

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.