Calculate previous date relative to row context date

DAX DAX Calculations
1

Hi I’m having a lot of trouble with something I would have thought is quite easy.

Essentially I’m trying to get a distinct count of document IDs that were in my fact table on the previous extract date (‘Upload Date’) relative to the date in the current row context.

What I think makes this slightly trickier is that there is no uniform frequency of the data extracts i.e. one on the 2 Oct, next on the 12 Oct and next after that on the 26 October etc. Also, the extract date is not the date field actively linked to my date table (although I do have inactive relationship set up for it).

image
image713×254 4.21 KB

In the image attached I’m trying to create a measure that gives me the total ID count on the previous ‘UploadDate’ i.e. next to the 12 Oct it should show the number of exceptions from the 2nd October (27563)

Thanks in advance for any support.

2

@hbreakey,

Welcome to the forum – great to have you here.

This measure should do the trick for you:

Prev Dist ID = 

VAR SelDate = SELECTEDVALUE( Data[Upload Date] ) 

VAR PrevDate = 
CALCULATE(
    MAX( Data[Upload Date] ),
    FILTER(
        ALL( Data ),
        Data[Upload Date] < SelDate 
    )
) 

VAR Result = 
CALCULATE(
    MAX(Data[Distinct ID Count by Upload Date] ),
    FILTER( ALL( Data ), Data[Upload Date] = PrevDate )
) 

RETURN
Result

image

The previous row pattern in Power PI can be tricky initially, particularly if you come from an Excel background where it is handled entirely differently. To understand this pattern and all its major variations, check out the entry on this pattern written by Enterprise DNA Expert @Greg:

I hope this is helpful.

– Brian

3

Awesome, thanks Brian - that does the trick.

I’d been playing around with similar variations of that calc the whole day and realised from your version that I had forgotten to use ALL(…), within the filter function table field to remove the current context!

Really appreciate the quick turnaround.

1 Like