Forum Discussion

errorssuck's avatar
errorssuck
Frequent Visitor
9 years ago

YTD measure based on different date fields

Hi,

 

I have a Fact table with 2 date fields. One is a Date field(20170101) and the other is Month field (201701) and I have created a relationship between this fact table with 2 copies of date table. Like as shown below: Now, I am trying to create one YTD measure on Qty field that should work with Month and Year columns in both date tables (Payment Month and Purchase Date).

 

I marked Payment Month and Purchase Date tables as date tables in PowerPivot and wrote the following DAX which seems to working in PowerPivot but doesnt work in PowerBI or SSAS tabular for some reason. 

 

Sales_YTD2:=CALCULATE([TotalSales],DATESYTD('PurchaseDate'[Date]),DATESYTD('PaymentMonth'[MonthEndDate]))

 

(where TotalSales = Sum(Qty))

 

Any help with the DAX is greatly appreciated.!!!

 

Need Help

1 Reply

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi errorssuck,

     

    Does your "PaymentMonth" table have a date column which contains continuous dates? Or it only contains month end dates?

     

    As most Time Intelligence Functions (DAX) require an individual and continuous date table to work correctly, please make sure both of your date table have a continuous date column.:smileyhappy:

     

    Regards