cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Murali777
Helper III
Helper III

Monthwise total using DAX

Hi,

 

Below is my request to figure out the MonthwiseTotal column using DAX measure. I have tried using TotalMTD function but it is working. Can any one pls help on this.

 

Murali777_0-1653632979063.png

 

2 ACCEPTED SOLUTIONS
JamesFr06
Impactful Individual
Impactful Individual

The table

JamesFr06_0-1653634181098.png

the measure ==>

JamesFr06_1-1653634233922.png

 

View solution in original post

esha_shah2002
Resolver II
Resolver II

hi @Murali777 

 

You can use this measure

MonthwiseTotal = CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[End Of Month]))
 
esha_shah2002_0-1653634690542.png

If this post helps, accept it as a solution.

View solution in original post

10 REPLIES 10
esha_shah2002
Resolver II
Resolver II

hi @Murali777 

 

You can use this measure

MonthwiseTotal = CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[End Of Month]))
 
esha_shah2002_0-1653634690542.png

If this post helps, accept it as a solution.

Hi Esha,

 

Is any input on the below scenario please?

 

Below is the code for the output Esha.. We need to filter based on the User right. it works fine. 

 

Thank you for your help as well.

 

MonthlyTotal1 = CALCULATE(SUM(EmployeeTable[Value]),ALLSELECTED(EmployeeTable[User]))

hi @Murali777 

No , I am not able to get the required output

 

Hi Esha,

Please see this example below, i have removed the user C an D from the second table but still it is summing all the users value. I need to sum only the selected user values. Please help me with this example.

 

Murali777_0-1653636012493.png

 

JamesFr06
Impactful Individual
Impactful Individual

The table

JamesFr06_0-1653634181098.png

the measure ==>

JamesFr06_1-1653634233922.png

 

Hi James,

 

It is calculating the total for all the users. if i added or removed some of the user, that time also it is showing the same total sum value. I need total for only selected users.

TomMartens
Super User
Super User

Hey @Murali777 

 

this article Time patterns – DAX Patterns contains almost everything you need to know to create time/date related calculations.

It's necessary that you create a dedicated calendar table and then create a measure that cumulates the values from all the data less and equal to the selected date.

 

Hopefully, this helps to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
JamesFr06
Impactful Individual
Impactful Individual

HI

 

Calculate(sum(value),allexcept(Table[EndofMonth]))

Hi James,

 

I have tried, but it is not working. if you dont mind pls share the PBIX file.

Thanks.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors