Forum Discussion

deb_power123's avatar
deb_power123
Helper V
4 years ago
Solved

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 wan...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

amitchandak's avatar
amitchandak
Super User
4 years ago

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"))
  • deb_power123's avatar
    deb_power123
    Helper V
    4 years ago

    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?

    • amitchandak's avatar
      amitchandak
      Super User
      4 years ago

      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