Forum Discussion

amitchandak's avatar
amitchandak
Super User
6 years ago

I think I did not get it completely. But to do any operation with time, create a calendar table. And join it with the date of you fact. Tell us what formula you want to drive(Functional not the power Bi formula)

 

In between refer

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/

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi amitchandak,

I sort of did create a calendar table for each situation... for example, for External Crews for the month of December in 2019, this is the table I created:

Distinct Dates ext Dec 2019 = calculatetable('Distinct Dates ext',FILTER('Distinct Dates ext',left('Distinct Dates ext'[Official Date],2)="12"),filter('Distinct Dates ext',right('Distinct Dates ext'[Official Date],4)="2019"))
 
Overall, I want to find the average days crews were productive. I have three filters. Internal/External, Month, and Year.
As of now, my average productive days formula works because when creating a seperate table, it filteres the Month/Year/Internal or External for me...
I want to create multiple measures, if possible, that takes into account Month, Year, and Internal/External