Trying to group calendar dates into months but they keep repeating

Anonymous — Thu, 11 May 2023 13:53:48 GMT

I have a table where I'm trying to have a line chart by month. The source table has a date and a month, but the month is sorted alphabetically and it won't allow me to sort column based on the date. So I tried creating various formulas to extract the month and year which looks fine, but when I add to the chart it adds an instance for every day of the month. For example, there are 30 instances of 'April-23', one for each day. I feel like I'm missing something simple here...

Re: Trying to group calendar dates into months but they keep repeating

PoweredOut — Thu, 11 May 2023 14:10:02 GMT

Create a Date Table and a relationship between your source table date column and the new date table date column. This will allow for grouping of months.

Date = //************** Script developed by RADACAD - edition: July 2021 //************** set the variables below for your custom date table setting var _fromYear=2021 // set the start year of the date dimension. dates start from 1st of January of this year var _toYear=2022 // set the end year of the date dimension. dates end at 31st of December of this year var _startOfFiscalYear=7 // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7 //************** var _today=TODAY() return ADDCOLUMNS( CALENDAR( DATE(_fromYear,1,1), DATE(_toYear,12,31) ), "Year",YEAR([Date]), "Start of Year",DATE( YEAR([Date]),1,1), "End of Year",DATE( YEAR([Date]),12,31), "Month",MONTH([Date]), "Start of Month",DATE( YEAR([Date]), MONTH([Date]), 1), "End of Month",EOMONTH([Date],0), "Days in Month",DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1, "Year Month Number",INT(FORMAT([Date],"YYYYMM")), "Year Month Name",FORMAT([Date],"YYYY-MMM"), "Day",DAY([Date]), "Day Name",FORMAT([Date],"DDDD"), "Day Name Short",FORMAT([Date],"DDD"), "Day of Week",WEEKDAY([Date]), "Day of Year",DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1, "Month Name",FORMAT([Date],"MMMM"), "Month Name Short",FORMAT([Date],"MMM"), "Quarter",QUARTER([Date]), "Quarter Name","Q"&FORMAT([Date],"Q"), "Year Quarter Number",INT(FORMAT([Date],"YYYYQ")), "Year Quarter Name",FORMAT([Date],"YYYY")&" Q"&FORMAT([Date],"Q"), "Start of Quarter",DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1), "End of Quarter",EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0), "Week of Year",WEEKNUM([Date]), "Start of Week", [Date]-WEEKDAY([Date])+1, "End of Week",[Date]+7-WEEKDAY([Date]), "Fiscal Year",if(_startOfFiscalYear=1,YEAR([Date]),YEAR([Date])+ QUOTIENT(MONTH([Date])+ (13-_startOfFiscalYear),13)), "Fiscal Quarter",QUARTER( DATE( YEAR([Date]),MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,1) ), "Fiscal Month",MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1, "Day Offset",DATEDIFF(_today,[Date],DAY), "Month Offset",DATEDIFF(_today,[Date],MONTH), "Quarter Offset",DATEDIFF(_today,[Date],QUARTER), "Year Offset",DATEDIFF(_today,[Date],YEAR) )

Credit: https://radacad.com/all-in-one-script-to-create-calendar-table-or-date-dimension-using-dax-in-power-bi

Re: Trying to group calendar dates into months but they keep repeating

Anonymous — Thu, 11 May 2023 14:13:07 GMT

THanks!

Re: Trying to group calendar dates into months but they keep repeating

Anonymous — Thu, 11 May 2023 14:13:10 GMT

THanks!

topic Re: Trying to group calendar dates into months but they keep repeating in DAX Commands and Tips

Trying to group calendar dates into months but they keep repeating

Re: Trying to group calendar dates into months but they keep repeating

Re: Trying to group calendar dates into months but they keep repeating

Re: Trying to group calendar dates into months but they keep repeating