Forum Discussion
Advocate IIIDateadd lastdate from fact table
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
Thank you all!
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
4 Replies
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.
- amitchandak
Super User
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() ?
v-lili6-msftCommunity Support
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
