cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Get sum of max date/period

Hi,

 

I would like to ask for your help on how to get the sum of max period displayed

 

In the screenshot below, the totals are all in sum by column but I want to display in TOTAL in first 3 columns the sum of max period:

scenario screenshot.png

 

I don't know how to post my pbix here on this message, can't see the icon to upload pbix.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Get sum of max date/period

Hi,

I have solved for Adjustment.  Please solve for Cost and Expenses yourself.  You may dowload the PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Highlighted
Super User IX
Super User IX

Re: Get sum of max date/period

@summer18 , Try same for all measures adjustments, sales, cost and expense

lastnonblankvalue(Table[Date],Sum(Table[adjustments]))

 

and take the max of date/period in the visual, use invoice no as row/axis/not summarized



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User V
Super User V

Re: Get sum of max date/period

Hi @summer18 ,

 

Upoad your sample pbix using One Drive or Google Drive.

 

Regards,

Harsh Nathani

Highlighted
Super User IX
Super User IX

Re: Get sum of max date/period

@summer18 - If I understand correctly, it sounds like Lookup Min/Max (without the lookup) https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

or  a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper II
Helper II

Re: Get sum of max date/period

Thanks @amitchandak for always helping me. Unfortunately, the user wants to display all the periods but in TOTAL row he wants to see the value of max date of the invoice. The invoice number is single select but the fiscal period is multiple selection.
Highlighted
Helper II
Helper II

Re: Get sum of max date/period

Highlighted
Super User V
Super User V

Re: Get sum of max date/period

Hi,

I have solved for Adjustment.  Please solve for Cost and Expenses yourself.  You may dowload the PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Helper II
Helper II

Re: Get sum of max date/period

Thanks @Ashish_Mathur it works
Highlighted
Super User V
Super User V

Re: Get sum of max date/period

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors