Forum Discussion
Date table using calculated measures and columns
Hi All,
I need to create a Date Table [Data Table] using any possible DAX measures or DAX calculated columns. I need the output in the below expected format.Please suggest.
so I basically want to show current month, year, previous month along with the month start and end dates. I want it for the whole year for 2021,2022 and 2023..Please suggest
| Current Month | Year | CurrentMonthStart | CurrentMonthEnd | Previous Month | PreviousMonthStart | PreviousMonthEnd |
| January | 2022 | 01-01-2022 | 31-01-2022 | December | 01-12-2021 | 31-12-2021 |
| February | 2022 | 01-02-2022 | 28-02-2022 | January | 01-01-2022 | 31-01-2022 |
| March | 2022 | 01-03-2022 | 31-03-2022 | February | 01-02-2022 | 28-02-2022 |
| April | 2022 | 01-04-2022 | 30-04-2022 | March | 01-03-2022 | 31-03-2022 |
| May | 2022 | 01-05-2022 | 31-05-2022 | April | 01-04-2022 | 30-04-2022 |
deb_power123 , try like
Date2 = var _1 = ADDCOLUMNS( CALENDAR(date(2019,01,01), date(2021,12,31) ), "Year", year([date]) , "Month Start Date", eomonth([date],-1)+1, "Month End Date", eomonth([date],0), "Month", format([DAte],"MMMM"), "Previous Month Start Date", eomonth([date],-2)+1, "Previous Month End Date", eomonth([date],-1), "Previous Month", format(eomonth([date],-1),"MMMM")) return SUMMARIZE(_1,[Year],[Month Start Date],[Month End Date],[Month],[Previous Month],[Previous Month Start Date],[Previous Month End Date])if needed you can simply return _1 , no need of summarize
4 Replies
deb_power123 , Try a table like
Table = ADDCOLUMNS( GENERATESERIES(1,12,1), "Year", year(Today()) , "Month Start Date", Date(Year(today()),[Value],1), "Month End Date", eomonth(Date(Year(today()),[Value],1),0) , "Month", format(Date(Year(today()),[Value],1),"MMMM"), "Previous Month Start Date", eomonth(Date(Year(today()),[Value],1),-2)+1, "Previous Month End Date", eomonth(Date(Year(today()),[Value],1),-1) , "Previous Month", format( eomonth(Date(Year(today()),[Value],1),-2)+1,"MMMM"))Hi amitchandak It worked perfect but I want to know , what change should I make to your above formula to show data for 2021 year and 2020 year as well.
As in current formula it shows only for 2022 year but what if I want to show for 2019,2020, 2021..could you please suggest?
deb_power123 , try like
Date2 = var _1 = ADDCOLUMNS( CALENDAR(date(2019,01,01), date(2021,12,31) ), "Year", year([date]) , "Month Start Date", eomonth([date],-1)+1, "Month End Date", eomonth([date],0), "Month", format([DAte],"MMMM"), "Previous Month Start Date", eomonth([date],-2)+1, "Previous Month End Date", eomonth([date],-1), "Previous Month", format(eomonth([date],-1),"MMMM")) return SUMMARIZE(_1,[Year],[Month Start Date],[Month End Date],[Month],[Previous Month],[Previous Month Start Date],[Previous Month End Date])if needed you can simply return _1 , no need of summarize
New columns
Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")Start Month date= eomonth([Date],-1)+0
Start End date= eomonth([Date],-1)+0
Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)For measures, you can refer
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKArefer if needed
Power BI for Beginners - DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s
Power BI for Beginners - Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
