Month aggregate dimension

DAX DAX Calculations
1

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

Thanks
Swapnil

2

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

Thanks
Jarrett

Enterprise%20DNA%20Expert%20-%20Small

3

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

4

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.

Thanks
Jarrett

Enterprise%20DNA%20Expert%20-%20Small

5

image
image.png840×281 5.76 KB

Thats the data from fact table.

6

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)

/Amdi

7

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

8

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

image
image.png724×91 2.27 KB

Thanks for the tips and the pbix shared.

Thanks
Swapnil