Dateadd lastdate from fact table

[GoingIncognito]

Hi, all ya!

Question: I need to do get a spesific date from fact table that doesn't have all the dates, how do I accomplish this? I do have a calendar table but the max(calendar[date]) doesn't equal max(fact[date]). So I would need to go back in time from the date that is max(fact[date]).
The thing is I got 

CALCULATE( SUM(FACT[VALUE]),  DATEADD(LASTDATE(FACT[PDATE]), -3, MONTH) ) working just fine, BUT

CALCULATE( SUM(FACT[VALUE]),  DATEADD(LASTDATE(FACT[PDATE]), -6, MONTH) ) gives me a blank. I'm guessing this is due to fact table missing that date?

Thank you all!

[v-lili6-msft · Accepted solution]

hi  @GoingIncognito 

First, DATEADD is a Time-intelligence function, so you'd better add a dim date table in your model for calculation.

https://radacad.com/do-you-need-a-date-dimension

Second, for "I'm guessing this is due to fact table missing that date", this means that there is no data that before 6 months of LASTDATE(FACT[PDATE]).

For example: if current row context of LASTDATE(FACT[PDATE]) is 7/19/2019, when there is no data in 1/19/2019, it will return blank.

Third, "how do I get the last date of that month?", you could try this formula

EOMONTH(LASTDATE('FACT'[PDATE]),0)

Regards,

Lin

[amitchandak]

If your date is joined is date table, use date filter on date table. Yes if there is no date in date table or no data in fact this will not work.

Example

CALCULATE(SUM(Sales[Sales Amount]),(dateadd('Date'[Date],-1,MONTH)))

[GoingIncognito]

Additional question, how do I get the last date of that month? I can't wrap all that in a EOMONTH() ?

[Anonymous]

Your guess is correct. 

Perhaps appending the date table with the missing dates to solve it, if your report is build this way. 

A calculated table might come in handy, you can dax a CALENDER function with 

DATEADD(LASTDATE(FACT[PDATE]), -6, MONTH), as start date and 

DATEADD(LASTDATE(FACT[PDATE]), +3, MONTH) as end date.