Getting the total sum of all months per year

[info_algoritmia]

Hey

I have the following problem, I need a matrix visual where I can show the total sales by year and month, I have data from 2022 until 2025, what I'm trying to accomplish is to add another measure where I have sum the sales for ALL months within the same year

For example: from jan to dec 2020, the total sales are 100K, so in the table matrix I want the 100K shown in Jan, feb, march, etc. and so on, and when we arrive to 2021, to show the total sales of the months of 2021 instead of the 100K of 2020

Something like this: The measure column the 30617 is the sum of all sales from 2020 showing the same for each month, then in 2021 the number change to whatever all sales for 2021 are

Does this make sense? hopw you can help me thanks!

[V-yubandi-msft]

Hi @info_algoritmia ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we provided for your issue worked for you  or let us know if you need any further assistance?

Your feedback is important to us, Looking forward to your response. 

[V-yubandi-msft]

Hi @info_algoritmia ,

we wanted to check in as we haven't heard back from you. Did our solution work for you? If you need any more help, please don't hesitate to ask. Your feedback is very important to us. We hope to hear from you soon.

Thank You.

[V-yubandi-msft]

Hi @info_algoritmia ,

@ajaybabuinturi , has provided an good  solution that effectively meets your requirement to repeat the yearly total across each month while adjusting it for additional categories.

If possible, please consider sharing the PBIX file. It would be highly beneficial for others in the community who might encounter similar challenges.

If your issue is resolved, kindly mark this as the Accepted Solution to help others in the community find it easily.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[ajaybabuinturi]

Hi @info_algoritmia,

I believe you are asking about automatically change the year in Matrix visual when it's entered to next year.
Try with below steps to achive it.

Step1: Create a calculated column using below DAX whether the year is current or not.

IsCurrentYear = IF(YEAR('Table'[Date Column]) = YEAR(TODAY()), 1, 0)

 Step2: Add this calculated column to visual level filter and set value is 1 (1 means CY and 0 means not CY) which ensures that visual will be filtered current year data.

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[info_algoritmia]

Not sure whats the outcome, but what I need is this 

For example: from jan to dec 2020, the total sales are 100K, so in the table matrix I want the 100K shown in Jan, feb, march, etc. and so on, and when we arrive to 2021, to show the total sales of the months of 2021 instead of the 100K of 2020

@ajaybabuinturi 

[ajaybabuinturi]

Now I got it, you want to calculate the total sales by Year and Month. You can use following sample measure.

Total_Yr_Mnt_Profit = CALCULATE(SUM(Orders[Profit]), ALLEXCEPT(Orders, Orders[Date].[Year]))

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[info_algoritmia]

@ajaybabuinturi awesome, now if I add another column for example a product category, how do I modify the formula to behave same way yearly but now by product and month?

[ajaybabuinturi · Accepted solution]

If you want to add product category in the same measure you can directly add to the measure. Else if you need separate measure you can replace Orders[Date].[Year] column with product cat. Refer below sample measures.

Total_Yr_Mnt_Profit = CALCULATE(SUM(Orders[Profit]), ALLEXCEPT(Orders, Orders[Date].[Year], Orders[Category]))

Total_Cat_Profit = CALCULATE(SUM(Orders[Profit]), ALLEXCEPT(Orders, Orders[Category]))

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.