Measure calculated filtered by Lastdate date present in my table and before last available date !

[Falongi_82]

Hi all, see below my capture :

I have the following measures :

1) to find de last date in my table I made this :

M_LAST_PICKING_DATE = CALCULATE(MAX(Q_SAP_LINES_BY_FLOW[PICKING_DATE]),ALLCROSSFILTERED(Q_SAP_LINES_BY_FLOW))

2) to find before last available date :

M_PICKING _DATE-1 = 
VAR LASTPICKINGDATE = [M_LAST_PICKING_DATE]
VAR Date1 = CALCULATE(MAX(Q_SAP_LINES_BY_FLOW[PICKING_DATE]),FILTER(Q_SAP_LINES_BY_FLOW,Q_SAP_LINES_BY_FLOW[PICKING_DATE]<LASTPICKINGDATE))
RETURN Date1

If I put these 2 measures in card ==> results are ok (18/01 and 19/01)

I want to create 2 matrix to find my total lines by flow group for these 2 dates

To create this, I use 2 others measure named M_TOTAL_LINES_D and M_TOTAL_LINES_D-1

M_TOTAL_LINES_D-1 = 
VAR d = [M_PICKING _DATE-1]
VAR val = CALCULATE(SUM(Q_SAP_LINES_BY_FLOW[SAP_LINES]),Q_SAP_LINES_BY_FLOW[PICKING_DATE]=d)
RETURN
IF(ISBLANK(val),0,val)

M_TOTAL_LINES_D = 
VAR d = [M_LAST_PICKING_DATE]
VAR val = CALCULATE(SUM(Q_SAP_LINES_BY_FLOW[SAP_LINES]),Q_SAP_LINES_BY_FLOW[PICKING_DATE]=d)
RETURN
IF(ISBLANK(val),0,val)

if you take a look at the capture above, you will see that for the proactive swap flow I have a value (676 lines) displayed with a picking date not equal to 18/01/21!

I don't want to see this flow because no values for 18/01/21

and idem for the 2nd matrix, How can I remove the 0 for proactive Swap flow.

I'm beginner sorry and thx in advance for you help

Fabri

[Pragati11 · Accepted solution]

Hi @Falongi_82 ,

For 18/01/2021 date issue, try chnaging your DAX as follows:

M_PICKING _DATE-1 = 
VAR LASTPICKINGDATE = [M_LAST_PICKING_DATE]
VAR Date1 = CALCULATE(MAX(Q_SAP_LINES_BY_FLOW[PICKING_DATE]),FILTER(ALL(Q_SAP_LINES_BY_FLOW),Q_SAP_LINES_BY_FLOW[PICKING_DATE]<LASTPICKINGDATE))
RETURN Date1

In your below DAX you are checking if it is BLANK replace it with 0. Therefore you see a 0 value there. If you want this 0 to be shown as BLANK, modify your DAX as follows:

M_TOTAL_LINES_D = 
VAR d = [M_LAST_PICKING_DATE]
VAR val = CALCULATE(SUM(Q_SAP_LINES_BY_FLOW[SAP_LINES]),Q_SAP_LINES_BY_FLOW[PICKING_DATE]=d)
RETURN
val

Thanks,

Pragati

[Falongi_82]

Big Thx Pragati,

problem solved.

But can you please shortly explain me how the filter (all ...)  works in PBI.

I don't understand very well.

thx for all

Fabri

[Pragati11]

Hi @Falongi_82 ,

FILTER expression with ALL on a data table used within a CALCULATE function, calculates a value that will not be affected by any external filters coming from the data on the report. So, basically it's creating a output calculated on all the rows of your data.

For more details check this link:

https://docs.microsoft.com/en-us/dax/all-function-dax

Thanks,

Pragati