Need help with Quarter & Year calculation

[Himanshi0291]

Hi Team,

I am looking to calculate Quaterly and Yearly utilization based on billable hours but I do not have measure for Quarter & Year to allow calculation of the same. 

My query to calculate quaterly utilization is - 

Quarterly Utilization = DIVIDE(([Billable Hours])*100,[Working Days]*8*[PeopleNum]-[Leaves])

but this does not have Quaterly paramter yet.

I have a field named ' Trans date' in one of the table which shows me - Year, Quarter, Month, Day (For example, 2020, Qtr2, June, 28).

How do I arrive at Quater & Year to include them in my query? 

Thanks,

[amitchandak]

@Himanshi0291 , Try with time intelligence

Utilization =
DIVIDE([Billable Hours],[Working Days]*8*[PeopleNum]-[Leaves])

Quarterly Utilization = calculate([Utilization], datesqtd(Date[Date]))

or you may have to calculate individual QTD formula and use it.

Also Mark as % , no need multiply by 100

refer :

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[aj1973]

Hi @Himanshi0291 

I think you are looking to use the Time intelligence function of Power Bi, Like YTD and QTD. For that you will need to create Date table and link it to the  ' Trans date' column in order for the YTD and QTD to get you the result you are looking for.

May be a sample would help the community.

Regards