cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Last value in each category for each group over months

Here's another measure that returns exactly the same results as the previous one (I've created a model and checked it):

Last Value in Period = 
var __lastVisibleDate = MAX ( DimDate[Date] )
var __visibleOpportunitiesWithLastDates =
	FILTER(
		ADDCOLUMNS(
                        VALUES( DimOpportunity[OpportunityId] ),
			"LastDate",
				CALCULATE (
					LASTNONBLANK( DimDate[Date], [Total Amount] ),
					DimDate[Date] <= __lastVisibleDate
				)
		),
		NOT ISBLANK( [LastDate] )
	)
var __lastValueInPeriod =
	CALCULATE(
	    [Total Amount],
            TREATAS (
                __visibleOpportunitiesWithLastDates,
                DimOpportunity[OpportunityId],
                DimDate[Date]
            )
    )
RETURN
	__lastValueInPeriod

There are no crashes for any of the two measures. They work like magic returning the same thing.

 

Best

Darek

tnovackova Frequent Visitor
Frequent Visitor

Re: Last value in each category for each group over months

Hi Darek,

 

yes, this also works, but it does YTD and I need actual stage of an opportunity:

 

image.png

In this picture you can see that the stages are YTD. But an opportunity has only one stage at a time. I need to see only the max stage for every month. Is that possible?

 

Thank you 🙂

 

Tereza

Super User
Super User

Re: Last value in each category for each group over months

It's definitely not YTD. If you select a period of time, it'll return the sum of the latest amounts for all the opportunities in the current context, where "latest amount" means the most current amount as visible before or on the last date visible in the current context. I've checked it. Try to slice by one opportunity at a time and see what you get.

Best
Darek
tnovackova Frequent Visitor
Frequent Visitor

Re: Last value in each category for each group over months

Hi Darek,

 

I am sorry for the late reply but I was out of office. When I filter just one opportunity (number 1), I see a chart that I send you earlier (tha last picture). And there is for every month every stage that the opportunity have had. I need just the last stage (with the biggest ID). Is it possible?

 

Thank you,


Tereza

Super User
Super User

Re: Last value in each category for each group over months

Sorry but it seems I can't understand your requirements with the current amount of information.

I won't be able to help you. Sorry.

Best
Darek

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 276 members 2,625 guests
Please welcome our newest community members: