Forum Discussion

RichardsonMM's avatar
RichardsonMM
Frequent Visitor
1 year ago
Solved

Matrix with Year and Month Column

I would like to make this excel table in Power BI, however i find some problems doing it, like, how can i use both year and month in the column of my matrix? I don't want to create two separated matr...
Need Help
  • krishnakanth240's avatar
    krishnakanth240
    1 year ago

    Hi RichardsonMM ,

     

    You can modify the DAX formula for your calendar table:

     

    Calendar =
    ADDCOLUMNS(
    CALENDAR(DATE(2023,1,1), DATE(2025,12,31)),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "PeriodLabel",
    IF(YEAR([Date]) < 2025,
    FORMAT([Date], "yyyy"),
    IF(MONTH([Date]) = 1, FORMAT([Date], "yyyy"), FORMAT([Date], "yyyy/mm"))
    ),
    "PeriodSort",
    IF(YEAR([Date]) < 2025,
    YEAR([Date]) * 100,
    IF(MONTH([Date]) = 1, YEAR([Date]) * 100, YEAR([Date]) * 100 + MONTH([Date]))
    )
    )

     

krishnakanth240's avatar
krishnakanth240
Icon for Super User rankSuper User
1 year ago

Hi RichardsonMM ,

 

You can modify the DAX formula for your calendar table:

 

Calendar =
ADDCOLUMNS(
CALENDAR(DATE(2023,1,1), DATE(2025,12,31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"PeriodLabel",
IF(YEAR([Date]) < 2025,
FORMAT([Date], "yyyy"),
IF(MONTH([Date]) = 1, FORMAT([Date], "yyyy"), FORMAT([Date], "yyyy/mm"))
),
"PeriodSort",
IF(YEAR([Date]) < 2025,
YEAR([Date]) * 100,
IF(MONTH([Date]) = 1, YEAR([Date]) * 100, YEAR([Date]) * 100 + MONTH([Date]))
)
)