topic Re: Last value in each category for each group over months in DAX Commands and Tips

Last value in each category for each group over months

tnovackova — Tue, 13 Aug 2019 11:12:59 GMT

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:

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:

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

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

Anonymous — Tue, 13 Aug 2019 11:24:06 GMT

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

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

tnovackova — Tue, 13 Aug 2019 11:34:20 GMT

Hi Darek,

here is my diagram:

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

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

Anonymous — Tue, 13 Aug 2019 12:49:04 GMT

[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

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

tnovackova — Tue, 13 Aug 2019 13:08:19 GMT

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

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

Anonymous — Tue, 13 Aug 2019 13:18:25 GMT

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

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

tnovackova — Tue, 13 Aug 2019 13:22:59 GMT

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

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

Anonymous — Tue, 13 Aug 2019 13:34:52 GMT

[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

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

Anonymous — Tue, 13 Aug 2019 13:38:02 GMT

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

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

tnovackova — Tue, 13 Aug 2019 13:45:09 GMT

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

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

Anonymous — Tue, 13 Aug 2019 14:54:54 GMT

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

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

tnovackova — Wed, 14 Aug 2019 06:58:25 GMT

Hi Darek,

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

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

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

Anonymous — Wed, 14 Aug 2019 09:03:16 GMT

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

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

tnovackova — Fri, 16 Aug 2019 10:01:23 GMT

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

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

Anonymous — Fri, 16 Aug 2019 12:44:56 GMT

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