Forum Discussion

peterpan's avatar
peterpan
Icon for Helper I rankHelper I
2 years ago

DATEADD Issue - Calculating from Begining of Time

I'm using three tables- Sales (Fact table), Product Table (Dimension) related by Product Key and Calendar Lookup Table, related by Order Date. I am trying to find Productwise Last Month Sales using following formula 

Previous Month Revenue = 
CALCULATE(
    [Total Revenue],
    DATEADD(
        'Calendar Lookup'[Date],
        -1,
        MONTH
    )
)

 But I'm getting Revenue from begining of time till last month of that product. However, if I put Month start date instead of Product, I get correct last month revenue. I am not able to understand why this inconsistent behaviour by the same function.

 

Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • shafiz_p's avatar
    shafiz_p
    Icon for Super User rankSuper User
    2 years ago

    Hi peterpan  You are getting wrong result because no date provided. Without date your formula unable to go previous time frame. It needs a start point. Your second result working because your measure now know where to start and where to go. Use date filter either in rows or columns or slicer.

     



    Hope this helps!!
    If this solve your problem, please accept it as a solution!!

    Best Regards,
    Shahariar Hafiz