Forum Discussion

CuriousGuy001's avatar
CuriousGuy001
Helper I
2 years ago
Solved

Group Multiple Values into One Values on Month

Hi everyone,

 

I am building a reporting using Matrix based on sales rep by points and date. I already finished my DAX calculation for filtering to MTD/QTD/YTD, but now I am trying to figure out how put all the sames months into total for each month. Relationship tables are connected, Formatted to Date - (mmmm yyyy). I did asked another community, and one possible solution is to get it to Calendar format, but I am not quite sure how to do it.

 

DAX calculation,

 

MTD/QTD/YTD Selection =
    VAR TodayDate = TODAY()
    VAR YearStart = CALCULATE(STARTOFYEAR('Margin Report'[Install Date]), YEAR('Margin Report'[Install Date]) = YEAR(TodayDate))
    VAR QuarterStart = CALCULATE(STARTOFQUARTER('Margin Report'[Install Date]),YEAR('Margin Report'[Install Date]) = YEAR(TodayDate), QUARTER('Margin Report'[Install Date]) = QUARTER(TodayDate))
    VAR MonthStart = CALCULATE(STARTOFMONTH('Margin Report'[Install Date]), YEAR('Margin Report'[Install Date]) = YEAR(TodayDate), MONTH('Margin Report'[Install Date]) = MONTH(TodayDate))
    Var Result =
    UNION(
        ADDCOLUMNS(
            CALENDAR(YearStart,TodayDate),
            "Selection", "YTD"
        ),    
         ADDCOLUMNS(  
            CALENDAR(QuarterStart,TodayDate),
            "Selection", "QTD"
        ),    
        ADDCOLUMNS(  
            CALENDAR(MonthStart,TodayDate),
            "Selection", "MTD"
        )
    )
RETURN
Result
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi CuriousGuy001 

     

    Here's a concise action plan to help you achieve this:

    1. Create a Calendar Table: If you haven't already, create a calendar table that covers the entire range of dates present in your sales data. This table should include columns for Year, Quarter, Month, and Date at a minimum. You can use DAX to create this table. For more details on creating a calendar table, please refer to the Creating Calendar Table in Power BI using DAX Functions - RADACAD documentation.

    2. Establish Relationships: Ensure that there's a relationship between your calendar table and your sales data table ('Margin Report' in your case) based on the date columns. 

    3. Modify Your Matrix Visual: In your matrix visual, use the Month column from your calendar table as the row or column headers to group your data by month. This will automatically aggregate your sales data by the months.

    4. Adjust Your DAX Calculation: If necessary, adjust your DAX calculation to reference the appropriate date column in your calendar table instead of directly from the 'Margin Report' table. This ensures that your calculations are leveraging the structured and consistent date information from your calendar table.

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi CuriousGuy001 

     

    Here's a concise action plan to help you achieve this:

    1. Create a Calendar Table: If you haven't already, create a calendar table that covers the entire range of dates present in your sales data. This table should include columns for Year, Quarter, Month, and Date at a minimum. You can use DAX to create this table. For more details on creating a calendar table, please refer to the Creating Calendar Table in Power BI using DAX Functions - RADACAD documentation.

    2. Establish Relationships: Ensure that there's a relationship between your calendar table and your sales data table ('Margin Report' in your case) based on the date columns. 

    3. Modify Your Matrix Visual: In your matrix visual, use the Month column from your calendar table as the row or column headers to group your data by month. This will automatically aggregate your sales data by the months.

    4. Adjust Your DAX Calculation: If necessary, adjust your DAX calculation to reference the appropriate date column in your calendar table instead of directly from the 'Margin Report' table. This ensures that your calculations are leveraging the structured and consistent date information from your calendar table.

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • CuriousGuy001's avatar
      CuriousGuy001
      Helper I
      2 years ago

      I had to re-do everything. So the one thing I did is to make another column based on Month only from the Install Date Column. And drag the month to the row to be display in the Matrix visualization.