cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tnovackova Frequent Visitor
Frequent Visitor

Last value in each category for each group over months

Hi everyone,

 

I have data about opportunities. Important columns for my question are OpportunityId, Date, Stage (in what stage opp is - Contacted, Verbal yes...) and Amount. Whenever something about an opportunity changes, its in new row (unchanged values remain the same). 

 

My assignment is that I have to make a chart (Stacked Column Chart in Power BI Desktop) which shows months on rows, values on columns and stage as legend. Also I have a filter that filteres just rows, that shows last stage of every opportunity in each month (if stage changes in one month, I want to see the last one a the last value). Chart should look like this:

 

image.png

 

 

 

 

 

 

 

 

 

On the first look it seems fine. But when I filter data just for one opportunity, I want to see last stage in every month, not just in months where some change happened:

 

image.png



 

 

 

 

 

 

In the picture above there is no data for May. But the opportunity didn't change since April and in May there should be the same value and stage (as in April). 

What should I do? 

 

Thank you very much for your help!

 

Tereza

 

 

14 REPLIES 14
Super User IV
Super User IV

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

Please paste your model layout in here. I want to see how your tables are connected and if you have a proper Date table.

Best
Darek

tnovackova Frequent Visitor
Frequent Visitor

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

Hi Darek,

here is my diagram:

 

image.png

Maybe I didn't explain it correctly. Now the model works fine, there is no mistake, I just want a different result. In my fact table there is no data change for the opportunity (selected in the second picture) in May, but I want to see in every month the last stage and amount (even if in that month there is no new data for that opportunity). I filtered some opportunity just for you to see my problem.

 

Tereza

 

Super User IV
Super User IV

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

[Total Amount] = SUM ( FactPipes[Amount] ) -- first basic measure

[Last Value in Period] = -- your final measure
var __lastVisibleDate = MAX ( DimDate[Date] ) -- DimDate[Date] should be related to FactPipes[Date]
var __datesWithValueByOpportunity =
	GENERATE(
		VALUES ( DimOpportunity[DimOpportunityId] ),
		var __lastDateWithValue =
			CALCULATE (
				MAX( FactPipes[Date] ),
				FactPipes[Date] <= __lastVisibleDate,
				ALL( DimDate )
			)
		RETURN
			{ __lastDateWithValue }
	)
var __lastValueInPeriod =
	CALCULATE(
		[Total Amount],
		TREATAS(
			__datesWithValueByOpportunity,
			DimOpportunity[DimOpportunityId],
			DimDate[Date]
		)
	)
RETURN
	__lastValueInPeriod

Try this one out.

 

Best

Darek

tnovackova Frequent Visitor
Frequent Visitor

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

I tried it and it killed my tabular instance (repeatedly). 

Super User IV
Super User IV

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

Well, you have to re-formulate the measure using different techniques. Either your fact table is too big or your computer is too weak. I'll try to re-write it and post once ready.

Best
Darek
tnovackova Frequent Visitor
Frequent Visitor

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

My fact table has 59 rows, it is really small and just for testing purposes. The whole model is very small. And my computer is a work computer and I work on it with much bigger databases. Maybe the calculation is too complicated, but I am not an expert in DAX at all, so I don't know how to make it better.

 

Thank you very much for your help :). If you manage to simplify the formula, it would be awesome.

 

Best wishes


Tereza

Super User IV
Super User IV

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

[Total Amount] = SUM ( FactPipes[Amount] )


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

Try the above... How many rows do you have in your fact table?

 

Best

Darek

Super User IV
Super User IV

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

Can you please paste a link to your file? Please put the file on OneDrive or somewhere I could reach it. Do not use Google Drive as I can't access it from work. I wanna see the crashes. This measure should execute immediately on such a small file.

Thanks.

Best
Darek
tnovackova Frequent Visitor
Frequent Visitor

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

I am sorry, I am not at work anymore. I will try the formula tomorrow. And about the data, I will put it online also tomorrow morning.

Thank you for your help

Tereza

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors