Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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,
Solved! Go to Solution.
Here's a concise action plan to help you achieve this:
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.
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.
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.
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.
Here's a concise action plan to help you achieve this:
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.
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.
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.
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
36 | |
21 | |
19 | |
15 |
User | Count |
---|---|
125 | |
37 | |
29 | |
29 | |
24 |