Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tnovackova
Regular 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
Anonymous
Not applicable

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

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

 

Anonymous
Not applicable

[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

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

Anonymous
Not applicable

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
Anonymous
Not applicable

[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

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

Anonymous
Not applicable

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

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
Anonymous
Not applicable

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

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

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors