Issue with using MAX as a filter for date in CALCULATE DAX Formula

[PBIFCSuperUser]

Hi all,

Please see formula: 

Part Sales.Daily= calculate(DISTINCTCOUNT('PartOrders’ [PartNo]),FILTER(ALL(‘PartOrders’[Date]),’PartOrders’[Date]=[MAX DATE]))

The measure of Max Date is: 

sorry the MAX Date measure is MAX DATE = MAX(’PartOrders’[Date])

The max date that I have in the formula is 11 January.

This formula works for all totals in a matrix table .

The issue is when I apply another column into the table matrix (i.e sales person name), it is giving the total distinct count number for each sales person in the following way:

if the person has sales on the 11 January, the 11 January total distinctcount will be returned from the formula. if the person did not have any sales on 11 January but did at some stage of the month (i.e last sales they made were on the 10th Jan) the sales from that last day they worked will be returned.

What I want is to be able to get a total count for only the last day that exists in the data, i.e total for 11th of January only, not includive of any sales made on any other dates.

Example of what is being returned in Matrix table:

see above, Fran has made it into the table however her sales are only on the 10th and not on the 11th. The total 366 does not include the 68 however when listed her line is included/

Any  help would be greatly appreciated! Please advise if you require any further information 🙂

Cheers,

Dom

[Datatouille]

Hi @PBIFCSuperUser

Your idea of using interim measures is good !

But using a measure inside a filter function activates context transition.

In your particular case, in the filter function, you are iterating over each date in your 'PartOrders' table (row context). Because you are using [MAX Date] (which is a measure) as part of the filter condition, this row context is transformed into an equivalent filter context (context transition) and your measure [Max Date] always returns the value of the date iterated. As a result, your filter condition is always true and nothing gets filtered.

Here are my suggestions:

Always use interim measures in the 1st argument of Calculate ( [expression] argument) - it makes the syntax clearer and never has any impact on performance/results.

Only use measures inside Filter arguments when you really want context transition to happen.

So here, create a measure like this: 

DsctPartNo = DISTINCTCOUNT('PartOrders’ [PartNo]

Then:

1) If you are using Excel 2010 or 2013:

[PartSales.Daily] = Calculate( [DsctPartNo] , FILTER(ALL(‘PartOrders’[Date]) ,’PartOrders’[Date]=MAX(’PartOrders’[Date])))

2) If you are using Excel 2016 or Power BI Desktop, you can leverage DAX variables:

[PartSales.Daily] = VAR MaxDate = MAX(’PartOrders’[Date]) RETURN

Calculate( [DsctPartNo] , ‘PartOrders’[Date]=MaxDate)

There are many pros using DAX variables. It is not just about measure readibility but also performance.

Here, option 2) returns the same result as option1) but MaxDate variable is evaluated first, "once and forever", in its current filter context. Its value is fixed so there is no need to use Filter function anymore !

The pros I see using variables (DAX 2015 so only in Excel 2016 or Power BI Desktop) :
(i) The formula is smaller, easier to read and to maintain. Users can understand better the role of each component.
(ii) It prevents you from the classic error to forget your "All" function in the Table argument of your filter function.

(iii) The performance is a little bit better since MaxDate variable is only evaluated once and not at each iteration step (as it is in the option 1) ).

(iv) It is also very convenient when you want to filter a column based on the value of a measure but you don't want context transition to apply (as we did here by storing the desired filtered value in a variable).

Hope I've been clear 🙂 and it helps you !

[PBIFCSuperUser]

Hi @Datatouille!

Thankyou so much for your detailed response and your education! I din't know how useful variabels could be 🙂

Unfortunately though the solution did nto yield the desired results.

see here below (first table), total is 2 but many other counts above. It is showing count for all data not just the latest. In this data, a total of 3 would have been found on the final day of the data.

I think the issue here is actually the "created by column". You could call this the salesperson ID info column. Is there a way I can filter the text column (created by) so that it only shows the values related to the max date? It needs to be a dax formula variable so I can apply a SWITCH formula to switch between daily and MTD view.

Thanks once again for all your help!

[Datatouille]

Hi, sorry but I don't understand your comment. Could you attach an example ? Using Dropbox or anything else.

Thanks 🙂

[PBIFCSuperUser · Accepted solution]

I have fixed my issue:

What I did:

created max date table: 

Daily Date = SUMMARIZECOLUMNS(Sheet1[Created Date], FILTER(ALL(Sheet1[Created Date]),Sheet1[Created Date]=max(Sheet1[Created Date])))

used the new table in my daily calc formula:

 = CALCULATE (DISTINCTCOUNT(Sheet1[Voucher Id]),FILTER(ALL(Sheet1[Created Date]),Sheet1[Created Date]=MAX('Daily Date'[Created Date])))

created all values formula (what I call mtd:

mtdcount = CALCULATE (DISTINCTCOUNT(Sheet1[Voucher Id]))

created a table with: 

row 1: MTD

row 2: Daily

and used as visual slicer

Created switch formula for MTD/Daily slicer: 

MTD/Daily count: switch(FIRSTNONBLANK('MTD Dailyu'[Column1],'MTD Dailyu'[Column1]),"MTD",[mtdcount],"Daily",[dailycount])

Took me a while to think of but it works!

Thanks for your help once again - I love using the SUMMARIZECOLUMNS functions 🙂

Cheers,

Dom

[v-huizhn-msft]

Hi @PBIFCSuperUser,

Please mark the right reply as answer, so that other people can find the solution clearly and easily.

Best Regards,
Angelia Zhang