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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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

 

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

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"))

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

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors