TOTAL of the month (just)

%3CLINGO-SUB%20id%3D%22lingo-sub-1389855%22%20slang%3D%22en-US%22%3ETOTAL%20of%20the%20month%20(just)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1389855%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Community%2C%3C%2FP%3E%3CP%3EI%20need%20to%20have%20the%20all%20of%20the%20spending%20corresponding%20for%20the%20current%20month.%20I%20have%20tried%20TOTALMTD%20and%20DATESMTD%20but%20in%20the%20last%20case%20it%20added%20until%20last%20row%2C%20even%20if%20it's%20in%20the%20different%20month.%20But%20i'm%20just%20looking%20for%20a%20function%20that%20sums%20spending%20in%20this%20month%20and%20that%20updated%20as%20the%20calendar%20evolves.%3C%2FP%3E%3CP%3EThere%20are%20any%20function%20for%20that%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1389855%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1390418%22%20slang%3D%22en-US%22%3ERe%3A%20TOTAL%20of%20the%20month%20(just)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1390418%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F667005%22%20target%3D%22_blank%22%3E%40carlos1945%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20add%20an%20extra%20column%20(maybe%20hide%20it%20if%20you%20want)%20where%20you%20show%20the%20month%20by%20using%20MONTH(Date).%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20them%20use%20SUMIFS%20and%20check%20the%20MONTH%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20possibility%20-%26nbsp%3B%20depending%20on%20your%20data%20it%20may%20be%20easier%20to%20use%20a%20pivot%20table%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1390426%22%20slang%3D%22en-US%22%3ERe%3A%20TOTAL%20of%20the%20month%20(just)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1390426%22%20slang%3D%22en-US%22%3EThe%20best%20thing%20to%20do%20is%20to%20use%20PivotTable%20for%20your%20dataset%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1392373%22%20slang%3D%22en-US%22%3ERe%3A%20TOTAL%20of%20the%20month%20(just)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1392373%22%20slang%3D%22en-US%22%3EYes%20i%20know%20some%20ways%20to%20reach%20the%20goal%2C%20using%20IF%20measures.%20But%20i%20thought%20there%20might%20be%20some%20function%20DAX%20more%20direct%20to%20get%20the%20goal.%3CBR%20%2F%3E%3CBR%20%2F%3EBut%20It's%20ok.%20Thanks%20for%20your%20help%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1393959%22%20slang%3D%22en-US%22%3ERe%3A%20TOTAL%20of%20the%20month%20(just)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1393959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F667005%22%20target%3D%22_blank%22%3E%40carlos1945%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20shall%20work%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EValueMTD%3A%3DCALCULATE(SUM(Table1%5BValue%5D)%2CDATESMTD('Calendar'%5BDate%5D))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eif%20you%20have%20correct%20relationship%20with%20Calendar%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1399359%22%20slang%3D%22en-US%22%3ERe%3A%20TOTAL%20of%20the%20month%20(just)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1399359%22%20slang%3D%22en-US%22%3EThank%20you%20very%20mutch%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi Community,

I need to have the all of the spending corresponding for the current month. I have tried TOTALMTD and DATESMTD but in the last case it added until last row, even if it's in the different month. But i'm just looking for a function that sums spending in this month and that updated as the calendar evolves.

There are any function for that?

5 Replies
Highlighted

@carlos1945 

 

You could add an extra column (maybe hide it if you want) where you show the month by using MONTH(Date). 

You can them use SUMIFS and check the MONTH column.

 

Another possibility -  depending on your data it may be easier to use a pivot table?

 

Hope this helps.

 

Peter

Highlighted
The best thing to do is to use PivotTable for your dataset
Highlighted
Yes i know some ways to reach the goal, using IF measures. But i thought there might be some function DAX more direct to get the goal.

But It's ok. Thanks for your help
Highlighted

@carlos1945 

It shall work

ValueMTD:=CALCULATE(SUM(Table1[Value]),DATESMTD('Calendar'[Date]))

if you have correct relationship with Calendar

Highlighted
Thank you very mutch