Find the days per month from Create Date to Finish Date - using Direct Query

[joannerw]

I'm trying to create a table of days per month between the create date and finish date, which could span years. I have found a solution but it doesn't work for direct queries. Can anyone help? And I'm new to Power BI so may need a little extra help...

[v-lili6-msft]

hi @joannerw 

Just use this formula to create a new table

Table = FILTER(CROSSJOIN('Defect',CALENDARAUTO()),[Date]>=[Defect Create Date]&&[Date]<=[Completion Date])

Regards,

Lin

[joannerw]

Thank you for your reply. I'm unable to create a new table as the icon is greyed out. Is this because my data source is a direct query to an SQL database?

[v-lili6-msft]

hi @joannerw 

Do you mean that only "New measure" is available in the power bi desktop?

Regards,

Lin

[joannerw]

Only new measure and new column are available as shown.  

[v-lili6-msft]

hi @joannerw 

You need to update power bi desktop to the latest version, and now It supports mixed mode that you could use New table in direct query.

https://www.microsoft.com/en-us/download/details.aspx?id=58494

Regards,

Lin

[joannerw]

@v-lili6-msftI am currently running the version 2.73.5586.1501 64-bit (September 2019) and using Power BI Desktop RS which is hosted locally, and not on the cloud due to security issues.