DAX Calculation Help to get Last Sales Date's Sales Total by Customer

[chudson]

Hi, I am trying to calculate the last date sales # from a FactSales table as a measure for a table visualization.  Below is my DAX measure using MAX but when using this it takes the max date by customer and calculates their last date sales not the sales based on the actual last sales date in the entire dataset.  The measure works if summarized/a card but if drag in other fields to my table visualization it doesn't work.

LASTDATESALES = SUMX(FILTER(FactSales,MAX(FactSales[RevenueRecognitionDate])=FactSales[RevenueRecognitionDate]),FactSales[Revenue])+0

Below is a dummied down table to explain my situation.  The customers without sales on 10/18/18 should have this calculation of 0 instead of pulling in their last sales on whatever date that may be.  Can anyone help with a calculated measure here?  It would be much appreciated.

Customer	Last Date Sales (Actual Returned)	Last Date Sales (Should be returned)	Customer Last Sales date	Entire Dataset Last Sales date
A	$36,045	0	10/4/2018	10/18/2018
B	$3,422	0	10/1/2018	10/18/2018
C	$4,050	0	10/17/2018	10/18/2018
D	$25,753	$25,753	10/18/2018	10/18/2018
E	$15,872	0	10/5/2018	10/18/2018
F	$1,225	$1,225	10/18/2018	10/18/2018
G	$33,230	$33,230	10/18/2018	10/18/2018

Thanks,

Chris

[Ashish_Mathur]

Hi,

Assuming you have a Calendar Data and there is a relatiosnhip from the Date column of the base data table to the Date column of the Calendar Table, try this measure

=CALCULATE(SUM(FactSales[Revenue]),DATESBETWEEN(Calendar[Date],MAXX(ALL(Calendar),Calendar[Date]),MAXX(ALL(Calendar),Calendar[Date]))

In your visual, drag the Customer from the relevant Table.

Hope this helps.

[chudson]

Hi @OwenAuger,

Yeah that calculation worked for the last date sales.! So now to add to this, I have a lot of other calculations based on this last sale date (MTD, QTD, YTD, period over period calculations) and wanted to know if I'll have to re-create all these measures using the VAR & RETURN measures to get them to dynamically calculated as i use the revenue recognition date slicer?  Basically as i filter the revenue recognition date slicer, I want all of these measures to change like the lastsalesdate.

As an example my current MTD & QTD calculations are below to get an idea.

MTD = SUMX(FILTER(FactSales,YEAR(MAX(FactSales[RevenueRecognitionDate]))=YEAR(FactSales[RevenueRecognitionDate])&&MONTH(MAX(FactSales[RevenueRecognitionDate]))=MONTH(FactSales[RevenueRecognitionDate])&&DAY(MAX(FactSales[RevenueRecognitionDate]))>=DAY(FactSales[RevenueRecognitionDate])),FactSales[Revenue])+0

QTD = SUMX(FILTER(FactSales,ROUNDUP(MONTH(MAX(FactSales[RevenueRecognitionDate]))/3,0)=ROUNDUP(MONTH(FactSales[RevenueRecognitionDate])/3,0)&&YEAR(MAX(FactSales[RevenueRecognitionDate]))=YEAR(FactSales[RevenueRecognitionDate])&&MAX(FactSales[RevenueRecognitionDate])>=FactSales[RevenueRecognitionDate]),FactSales[Revenue])+0

Thanks,

Chris

[OwenAuger]

@chudson

Do you have a Calendar table? If not, I would recommend adding one.

To start with, it would mean you could use time intelligence functions to make your MTD/QTD/YTD formulas simpler by using DATESYTD, DATESQTD, DATESYTD.

Also, for the requirement to evaluate various measures in the context of the last sale date, rather than my original DAX approach, you could instead add a column to the Calendar table that is 1 only for the last sale date (since it is a global constant). You could then filter on this column on specific pages/visuals where you need the last sale date to be enforced.

Realise this is a general suggestion so please post back if needed.

Regards,

Owen

[chudson]

Hi @OwenAuger,

I had previously tried adding a colum in my calendar table for the last sale date but the problem with this is when i filter dates on my report pages the calculated column doesn't change so the measures don't work properly.  Is there a way to get a calculated column to filter as your slicers do?

Thanks,

Chris 

[OwenAuger]

@chudson

Hi Chris,

It sounds like the Last Sale Date needs to respond to filters...if that's the case then a calculated column won't work.

You will have to in some way repeat the filter within each measure. Regardless, I would recommend including a Calendar table to make your time intelligence formulas easier to write.

Just thinking, to help write the measures without repeating the logic, you could use a measure branching or measure selector approach.

Perhaps consider the approach in these articles/videos:

https://blog.enterprisedna.co/2018/08/01/what-is-measure-branching/

https://www.sqlbi.com/articles/optimizing-mutually-exclusive-calculations/

Regards,

Owen

Regards,

Owen

[OwenAuger · Accepted solution]

Hi @chudson

I would create a measure looking something like this:

LASTDATESALES =
VAR LastDateGlobal =
    CALCULATETABLE (
        LASTNONBLANK ( FactSales[RevenueRecognitionDate], 0 ),
        ALL ( FactSales )
    )
RETURN
    CALCULATE ( SUM ( FactSales[Revenue] ), KEEPFILTERS ( LastDateGlobal ) ) + 0

In short, this stores the global last date existing in the FactSales table in a variable, then sums Revenue in the current filter context intersected with this global last date, which will naturally include only Revenue occurring on that particular date.

If you want the last date to be subject to other filters, you could change ALL ( FactSales ) to ALLSELECTED ( ).

I wasn't sure if you had a separate Calendar table, but the above measure should work regardless.

Regards,

Owen