Create table of all dates in months from another table

[Anonymous]

Hi,

I have a table with different dates in it : 01/04/2020 and 01/05/2020. I need to create a table of all the dates in the months of the before mentioned date. So first I need to get the distinct months and then I need to get all the dates in those months. Is this possible to do in DirectQuery mode please? Thanks

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

Try to use below dax expression to create a new table:

New Table= CALENDAR(MIN('table'[date]),MAX('table'[date]))

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , you create using a calendar, That will convert it into the mixed mode.

You can have one in the database and mark it as date table here.

refer

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

Or

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/

[Greg_Deckler]

CALENDAR