Forum Discussion

kcantor's avatar
kcantor
Icon for Community Champion rankCommunity Champion
10 years ago

DAX: Two dates, one report page

I have a data set that contains a sales fact table, product table, and date table. Within the sales fact table, there are two dates: Order date and Posting Date with Posting Date being the date the invoice posted. Generally speaking, I only use Order Date. Now, however, I am being asked to show totals for a specific date (Lets say Jan 12, 2016) for orders placed on that date as well as totals for sales that posted for that date.
I believe I can do this but I do not know where to start. If there is a blog posting please point me in that direction, if not, can anyone help with the DAX necessary?

Need Help

2 Replies

  • greggyb's avatar
    greggyb
    Icon for Resident Rockstar rankResident Rockstar
    10 years ago

    Create two relationships between FactSale and DimDate. [Order Date] -> [Date] should be active since it's the one you normally use. [Posting Date] -> [Date] should be marked inactive.

     

    Ordered =
SUM( FactSale[SalesAmount] )

Posted =
CALCULATE(
    SUM( FactSales[SalesAmount] )
    ,USERELATIONSHIP( FactSale[Posting Date], DimDate[Date] )
)

Ordered and Posted =
[Ordered] + [Posted]

    USERELATIONSHIP() just tells the model which relationship to use when evaluating some expression.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    10 years ago

    The other, albeit hacky way, of doing this is to just import your date table twice and relate your table to both based upon the appropriate date field. Naming convention is important to avoid confusion. Not in any way optimal, but avoids having to write any DAX at all.