Month aggregate dimension

Hi All,

My fact table is at month and year level with column like period_num, Period_name and year.
would it be possible to create a Month aggregate dimension that has data like
MMYY, Mon-YY, Year using Dax formulae !? Please someone help


All you need to do is something similar like this formula with the date field you want to show like you mentioned.



Hi Jarrett,

I do not have date field anywhere. my fact table is at month level which shown as Mon YY in period name column.
I need to build a dimension table which has data at month level. that is one row per each month and year combination

Show an example of what it looks like in your data in. Also sharing a copy of your PBIX is a possibility to help better see what you want to accomplish.



Thats the data from fact table.

I understand you don’t have the date ex. 01-01-2017 as an column. You can try to load the excel file into PBI file. Power Query will maybe convert the date Jan-17 as 01-01-2017.
Power Query did that, in the test I will attach here.
In case it don’t. Maybe because the data source aren’t an excel file. Then I in the same attached files, I show how to ad a date column 01-01-2017. All happens in Power Query. Without code, You shall just choose and click.
In Power Query choose Add Column/Column From Example.
Save both files. Excel and PBI.
Change the path. Because it will be cut when you download.
From Power Query/File/Options And Settings/Data Source Setting
Swapnil.xlsx (86.9 KB)
Swapnil.pbix (98.2 KB)


Thanks for the help Amdi. I will try this and keep you posted.

Hi Amdi,

I am able get the first date of month using the technique you mentioned. however, i feel i need to discuss with business on how to allocate the ADJ-17 which is adjustment month in to a quarter. otherwise i will end up with below error

Thanks for the tips and the pbix shared.