cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
errorssuck Frequent Visitor
Frequent Visitor

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.!!!

 

Re.PNG

1 REPLY 1
v-ljerr-msft Super Contributor
Super Contributor

Re: YTD measure based on different date fields

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.Smiley Happy

 

Regards

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 44 members 1,057 guests
Please welcome our newest community members: