Calculated column in DirectQuery source not working.

[ROG]

Hi guys,

Can you please help me out in creating some calculated columns using the only date column I have from data source?

It's DirectQuery and I need toextract year, month, and week. 

I tried thebelow, but it's not possible.

Thank you

[v-xiaotang · Accepted solution]

Hi @ROG 

Thanks for reaching out to us.

You can try this,

create the measure

Measure = CALCULATE(COUNT(subscriber[subscriber]),FILTER(subscriber,YEAR(subscriber[date])= MIN('calendar'[year]) && MONTH(subscriber[date])=MIN([month])))

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@ROG , Try functions like day, month, year etc. As Power bi limits what you can do in direct query, I advise to bring them from the database

Power BI Decoding Direct Query: https://www.youtube.com/playlist?list=PLPaNVDMhUXGbKatyDdOhGbTL3xW2Xy6pA

[ROG]

Thanks @amitchandak 

I have the Calendar table from database, but I can't get the Number of subscribers by month/year using the columns from the calendar table. 
As you can see below, they're not connected directly. What should I do? 😕