Forum Discussion

GoingIncognito's avatar
GoingIncognito
Icon for Advocate III rankAdvocate III
6 years ago
Solved

Dateadd 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...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    GoingIncognito
    Icon for Advocate III rankAdvocate III
    6 years ago

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