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]))
    )
    )

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi RichardsonMM,

Thank you for reaching out in Microsoft Community Forum.

Thank you krishnakanth240, DataNinja777    for the helpful response.

As suggested by Krishnakanth240, DataNinja777., I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.

Please continue using Microsoft community forum.

Regards,
Pavan.