Forum Discussion
2 date fields in different table
having a calendar table makes your life much easier (because you get to use the time intelligence functions)
you can do those calculations without it but you'll have to write a bit more
look here at this link provided by Matt
http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/
I am sure it will work if I create a table with the field "date" and add all dates in it but it does't make sense.
At that point, I would prefer to add a column in YTD table called forecast and paste the Forecast data in it.
I tried that too by doing that but again it add all the total impression forecasted as a total...
Imp_Served = SUM(Forecast[Forecasted impressions])
It actually does make absolutely 100% sense if you understand that DAX and Power BI are all about context. As I explained above, you need a common context filter, otherwise the aggregation is going to get absolutely everything in the other table and just SUM it, etc. You could also relate your Date in Forecast to your Date in Impressions and then use the Date from Impressions as that provides the same, common context for calculations.
Wow, this is the first time I am seeing this in Excel! So no choice, the solution is to create a middle table that links the dates in both table. Thanks to both of you!
Eric
Oh sorry, I am not saying that DAX makes no sense but the way I pull my reports, having to always add dates in a table just for that is a huge pain but I understand what you are saying and yes it does make sense.
I will try to do a related between these two and will let you know ;) thanks for your support
Generally, I just create a separate query to whatever my same data source is and just pull in the dates, remove duplicates and presto, a date table, or use DateStream in the Azure Marketplace. I think there is a way to do it automagically with CALCULATETABLE but I would have to confirm that.
having a calendar table makes your life much easier (because you get to use the time intelligence functions)
you can do those calculations without it but you'll have to write a bit more
look here at this link provided by Matt
http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/
elatreille did you see you can copy and paste the code in the Query Editor - read how it works later
You can set set your first date here... so the calendar starts from that date - just make sure it is the earliest in your data set