DAX Measure to Create the Previous Date Sales

[PBI_enthusiast2]

 I have a measure that gives the sales over the given date in column B. I am trying to create another measure that would give me a results as in Column C (screenshot below).

Data Model:

 The measure I have tried, but did not worked as expected.

CALCULATE([Sales_Measure],  FILTER(ALLSELECTED(DimDate), DimDate[Date] <  MAX(DimDate[Date])))

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[CNENFRNL]

=
CALCULATE (
    LASTNONBLANKVALUE ( DimDate[DateKey], [Sales_Measure] ),
    DimDate[DateKey] < MAX ( DimDate[DateKey] )
)

[Greg_Deckler]

@PBI_enthusiast2 Try:

PD Measure =
  VAR __DateKey = MAX('DimDate'[DateKey])
  VAR __PDKey = MAXX(FILTER(ALL('DimDate'),[DateKey] < __DateKey)
  VAR __Result = CALCULATE([Sales Measure], 'DimDate'[DateKey] = __PDKey)
RETURN
  __Result

[Ashish_Mathur]

Hi @Greg_Deckler and @CNENFRNL.  Could you share the way to solve this with the new WINDOW() and INDEX() functions.

Thank you.

[CNENFRNL]

No comments as I haven't mastered these newly introduced functions. I'm personally no fan for these funcs because they (seem to) sabotage the conciseness and elegance of "old-school" DAX funcs; IMHO they seem to be some replica of Tableau table calculation funcs.