topic Re: Total Per Month in DAX Commands and Tips

Total Per Month

JB17 — Thu, 13 Feb 2025 09:14:36 GMT

Hi,

I need help with creating DAX for the following:

Instead of showing the value for each status per date per month, I need to show the total status per month in the desired result. thanks so much in advance!

Re: Total Per Month

bhanu_gautam — Thu, 13 Feb 2025 09:41:48 GMT

JB17 , Try using

dax
Total Per Month =
CALCULATE(
SUM('YourTable'[Value]),
ALLEXCEPT('YourTable', 'YourTable'[Status], 'YourTable'[Date].[Month])
)

Re: Total Per Month

AilleryO — Thu, 13 Feb 2025 09:49:10 GMT

Hi,

If your data are as simple as shown, you might not even need DAX. Just putting your value in a visual with the month of your date hierarchy would do it.

If you need more calculations, or if your data are not as simple as shown, then you should consider creating DAX measures.
Starting with: TOTAL VALUE = SUM( YourTAble[YourColumn] )

and then using that basic measure to build the more sophisticated ones.
TOTAL XXX = CALCULATE( [TOTAL VALUE] , .... )

Hope it helps

Re: Total Per Month

vivek31 — Thu, 13 Feb 2025 10:21:52 GMT

HI JB17,

first you shoud format your status column and create a new status column in table

new status = FORMAT('Table (6)'[status],"MMM-yyyy")

and create formula this type formula

per month Action = SUMX(VALUES('Table (6)'[new status]),SUM('Table (6)'[Action complete]))

per month Deferrd = SUMX(VALUES('Table (6)'[new status]),SUM('Table (6)'[Deferred]))

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

Thank you

Re: Total Per Month

chauhans85 — Thu, 13 Feb 2025 11:15:21 GMT

UnPivot all the dates column using Transfrm .

Then you Have three column status, Date, Values.generate Mobth from date aas Month

then you can you Matrx visul
Drag Column as below
Rows : STATUS
Columns :Month
Values: sum of values

Hope this helps

Re: Total Per Month

mark_endicott — Thu, 13 Feb 2025 16:20:53 GMT

JB17 - If I am right in my interpretation, all of your dates are in different columns, if this is the case you need to un-pivot your data in Power Query first, I have written a blog about this here: https://triangle.im/power-bi-mistake-5-why-pivoted-data-leads-to-power-bi-pain/

and you can find more guidance on doing it here: https://learn.microsoft.com/en-us/power-query/unpivot-column

Once you have done that you can add a new column to your table that will give you the month year in your desired format, or hook up a date table (this would be my choice) - here's a blog on that: https://blog.enterprisedna.co/introducing-power-query-extended-date-table-v2/

and simply sum the new column that has your values in.

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

Re: Total Per Month

Anonymous — Mon, 17 Feb 2025 07:54:12 GMT

Hi, JB17

Based on your information, I create a sample table:

Then unpivot columns, like this:

Select Close & Apply. Create a table visual, put the date, status, and values in the table, and then date selection to leave the month. Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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