Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
deb_power123
Helper V
Helper V

DAX calculated columns to create Date Tables

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 MonthYearCurrentMonthStartCurrentMonthEndPrevious MonthPreviousMonthStartPreviousMonthEnd
January202201-01-202231-01-2022December01-12-202131-12-2021
February202201-02-202228-02-2022January01-01-202231-01-2022
March202201-03-202231-03-2022February01-02-202228-02-2022
April202201-04-202230-04-2022March01-03-202231-03-2022
May202201-05-202231-05-2022April01-04-202230-04-2022
1 ACCEPTED SOLUTION

@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

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@deb_power123 ,

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
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/pow...

 

refer 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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi @amitchandak It doesnt work, it doesnt fetch the output expected.You try the formula in pbix,it wont fetch the desired answer

@deb_power123 , Try a new 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"))
Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi @amitchandak  If I want to show the data for 2019,2020 and 2021, what changes should I make to the above formula of yours?

At present it looks great but it calculates only for 2022 but I want to show 2019,2021 and 2020 data as well.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

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors