Totals in my aggregations not showing

DAX DAX Calculations
1

I have a sales history table where all (relevant) changes to sales are made as they progress through the pipeline.

For the purposes of this, the key fields are the timestamp the last change was made (datetime), the current stage in the pipeline, and the current value. What I’m trying to get to is to add the most recent values together for whatever date range I’m provided with. Here’s my current result

image
image856×346 87.2 KB

Here’s a snippet of the data

image
image946×571 225 KB

And here’s the data model

image
image590×600 46.9 KB

At any level of date granularity, I want to only consider the most recent transaction for the date and use the pipeline stage and value for all calculations.

This dax figures out when that most recent transaction was:
LastTransaction = CALCULATE(max(deals[Index]),REMOVEFILTERS((tblStage)))

Here’s the dax that created the first picture
testing1 =
var transactionID = [LastTransaction]
var dealFilter = filter(deals,deals[Index]=transactionID)
return
calculate(SUMX(deals,deals[psc_fee]),dealFilter)

I can see from the result that I nearly have what I want, but I can’t for the life of me figure out how to get the totalling working for a deals that aren’t the final one for the period.

I think I need to get the last transaction for each unique deal and only use those in the calculations - and I suspect I’m making a really simple error in my logic. I’ve only been using BI for a couple of months, and am new to thinking about data in this way - if I could write a SQL query, it’d be easy!

Any help would be greatly appreciated.

Edited to include a pbix with all the datatestTimeseries.pbix (121.8 KB)

2

Hi @flutterbybee, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

Hi @flutterbybee,

Sample file will come a long way to solve this problem. Sample PBIX file will be fantastic and it would become really easy to provide you solution.

Regards,
Zubair

4

I have edited the question to include a sample file

5

Hi @flutterbybee,

Please find attached the solved file. I have created below new measure which calculates value of amount on the last given day in the filter context.

New Measure =
   var lastdate1 = MAX('Deal History'[Transaction Date])
  return
CALCULATE(SUM('Deal History'[Amount]),FILTER('Deal History','Deal History'[Transaction Date]=lastdate1))

Below is the result and I hope it satisfies your requirement.

image
image790×221 11.2 KB

EDNA_testTimeseries_ValueatLastTransaction.pbix (121.9 KB)

6

Hi,

The result I’m looking for wouldn’t include any data for lead or negotiating because at the end of the period there is no data in either of those states. What I want is only the last history record for any deal in the period being considered.

Hopefully that makes sense.

7

Hi @flutterbybee,

Yes, it makes sense and its easy to do. Please find solution attached :slight_smile:

New Measure =

VAR lastdate1 = MAX('Deal History'[Transaction Date])

VAR MonthYear = MAX('Dates'[MonthYear])

return

CALCULATE(SUM('Deal History'[Amount]),FILTER('Deal History','Deal History'[Transaction Date]=lastdate1 && RELATED('Dates'[MonthYear])=MonthYear))

image
image825×204 10.9 KB

EDNA_testTimeseries_ValueatLastTransaction.pbix (121.8 KB)

1 Like
8

Hi,

This is still not quite correct for the problem I’m trying to solve. I would expect to only see one column populated for each dealID.
So in August, the only value for 2760783926 should be the 6978.77 in the Won Not Booked column. Everything else looks right.
I’m sorry to be fussy, I figure this is close, but everything I try to solve the issue is not quite doing the right thing for me.

9

Hi @flutterbybee,

This makes sense. Do you want to see table like below?

image

10

Yes I do, and I can get it to that stage, except it’s not totalling Proposals in that visual and that’s the bit I can’t for the life of me figure out!

When I do this for real, I’ll be looking at the totals and not the individual deals like I am in the example (where I’m trying to check if my result is correct).

11

For completeness, I think I found the solution. I’m happy it works in the sample data, so now I have to validate it with a larger set of data.

Last Value in Period = 
    var __lastVisibleDate = max(Dates[Date])
    var __visibleOpportunitiesWithLastDates =
	FILTER(
		ADDCOLUMNS(
			SUMMARIZE(
				'Deal History',
				'Deal History'[Deal ID]
			),
			"LastDate",
				CALCULATE (
					LASTNONBLANK ( Dates[Date], [Total Amount] ),
					Dates[Date] <= __lastVisibleDate, REMOVEFILTERS('Deal Stages')
				)
		),
		NOT ISBLANK( [LastDate] )
	)
    var __lastValueInPeriod = 
        calculate(
            [Total Amount],
            TREATAS(
                __visibleOpportunitiesWithLastDates,
                'Deal History'[Deal ID],
                Dates[Date]
            )
    )
return
    __lastValueInPeriod
1 Like