Total Per Month

[JB17]

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!

[Anonymous · Accepted solution]

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.

[mark_endicott]

@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!

[chauhans85]

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

[vivek31]

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

[AilleryO]

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

[bhanu_gautam]

@JB17 , Try using

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