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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors