Forum Discussion
Using query results to build date table using CALENDAR()
I have used this query here https://www.biinsight.com/finding-minimum-date-and-maximum-date-across-all-tables-in-power-query-in-power-bi-and-excel/ and it works, but I cannot work out how to use the results to generate a date table using the CALENDAR() function.
EDIT: I have 4 tables which I want to find out the Earliest and Latest date from as a whole, this is my reason for using this query, if there is a better way please let me know. Based on amitchandak reply it is not possible to use the results of the query, so how would I go about building a Date table based on the Earliest an Latest dates found in the 4 columns in these 4 tables (1 specific date column per table).
I currently generate a date table using CALENDAR(DATE(2020,1,1),DATE(2025,1,1)) , I would like to use the results of the query so the date table accurately reflects the dates in the Model but have no experience using query results in DAX.
Your help is much appreciated.
Thanks
2 Replies
hangell , A Dax table you will not be able to use in the power query
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1
For DAX calendar refer
1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5ee47de6a208example
Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)You can use min and max from another table into calendar function
DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145sSorry amitchandak I missed out an important piece of information as to why I am running this query. I have now updated the post.
