Forum Discussion

ap's avatar
ap
Icon for Advocate I rankAdvocate I
9 years ago

Problems with time intelligence in a role playing calendar table (MTD and QTD)

I'm using a role playing calendar table but running into problems when using time dimensions particularly MTD and QTD

 

I have a closed date and proposal due date all in one calendar table (DimClosedDate). It goes from 2015 to 2020.

 

The closed date is never before today’s date. However, the Proposal Due date is in the future. When I try to do a calculation for

 

Total Sales Closed ($CAD) MTD = CALCULATE([Total Sales Closed ($CAD)],DATESMTD(DimClosedDate[Date])

 

I get blank because I assume DAX looks at the current month in the future which is in 2020 (as I have a proposal due date in that year)

 

I also tried this but this doesn’t work also, I also get a blank.

CALCULATE([Total Sales Closed ($CAD)],DATESMTD(DimClosedDate[Date]),USERELATIONSHIP(FactOpportunity[Closed Date],DimClosedDate[Date]))

 

I’ve tried but neither work

 

Total Sales Closed ($CAD) MTD =

CALCULATE([Total Sales Closed ($CAD)],DATESMTD(DimClosedDate[Date]),Filter (DimClosedDate,DimClosedDate[Date] < Now() ))

 

Total Sales Closed ($CAD) MTD =

CALCULATE([Total Sales Closed ($CAD)],DATESMTD(DimClosedDate[Date]),Filter (DimClosedDate, DimClosedDate[YEAR]=year(now())))

 

Outside of creating date tables for each, is there anything I can only tell it to use the min and max of the closed date in the fact table? 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    ap,

    Could you please share sample data of your fact table and calendar table? What field you use to create relationship between the two tables?

    Also, we need to know what formula you use to create [Total Sales Closed ($CAD)] and what is the result when you use the following DAX to calculate MTD.

    Total Sales Closed ($CAD) MTD= TOTALMTD(SUM(FactOpportunity[column]), FactOpportunity[Closed Date])

    Regards,