Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I am new to Power BI and I will need some help here. I have 3 tables where one is a middle one and the two others has the full data. In the first table I have historical data and in the other one I have forecasted data. Both has a date field. When putting a chart I would like to show the historical data and what was forecasted to a specific date. in the example below, I should see my 2 lines on top of each other for 2 days April 29 and 30. How can I do that? when I select my 2 fields and select the date field, the graph shows me two straigh line! Thanks! Eric
Date | 26-Apr-16 | 27-Apr-16 | 28-Apr-16 | 29-Apr-16 | 30-Apr-16 | 1-May-16 | ||
Impressions served (table A) | 100 | 200 | 300 | 250 | 275 | 300 | ||
Impressions Forecasted (Table B) | 225 | 300 |
Solved! Go to Solution.
Found it, CALENDAR and CALENDARAUTO
https://msdn.microsoft.com/en-us/library/dn802546.aspx
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 have three tables:
ImpressionDates
Date
Tuesday, April 26, 2016 |
Wednesday, April 27, 2016 |
Thursday, April 28, 2016 |
Friday, April 29, 2016 |
Saturday, April 30, 2016 |
Sunday, May 1, 2016 |
ImpressionsServed
DateImpressions
Tuesday, April 26, 2016 | 100 |
Wednesday, April 27, 2016 | 200 |
Thursday, April 28, 2016 | 300 |
Friday, April 29, 2016 | 250 |
Saturday, April 30, 2016 | 275 |
Sunday, May 1, 2016 | 300 |
ImpressionsForecasted
DateForecast
Friday, April 29, 2016 | 225 |
Saturday, April 30, 2016 | 300 |
Relationships are:
Visualization is:
Ok, I get that but what if my dates are in each table with the data...
Do I need to have a table with the field date and fill it with each date of the calendar?
Each table have around 500 lines.
Thanks
Eric
@elatreille - I'm not understanding what you are asking "dates are in each table with the data". Yes, my model has dates in each table with the data. Can you explain a little more or post a screen shot of your tables or something?
You would not necessarily need a separate date table with just a list of all dates if one of your tables includes all of the dates that you care about and has no repeat values.
Your graph is exactly what I need. Here is what I have:
Sorry for the picture, for that metrics I am not using any measures, hope that screenshot is better
Maybe one of those is a cumulative/running total? Why is the difference so much?
Try creating Runing Totals for each like so
Forecast RT = CALCULATE ( SUM(Table[Forecast], FILTER(ALL(Table), Table[Date] <=MaX(Table[Date]) ) )
Actual RT = CALCULATE ( SUM(Table[Actual], FILTER(ALL(Table), Table[Date]<=MAX(Table[Date]) ) )
I'll try that but to answer your question, depending the date I select, one will show and dupicate the total.
So if I select the date under forecast, I will have the forcast per day with no probelem and the impression served will be the total of all impressions, if I select the date under Impression served, I will have the impressions per day with no prob and the forecast will be the total forecasted impressions 😞
I'm pretty sure that if you create a table of just dates and relate both Impressions and Forecast to it like in my model, you will get the correct lines.
Here is what is going on, it just dawned on me. Because there is no relationship between Forecast and Impressions, it is doing exactly what you said, it is simply summing one or the other (all of them) for every date because there is no context filter for date essentially. So the aggregation gets everything. That is why one is so much higher than the other and why it is the same number for all dates.
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.
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
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/
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
@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
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.
Found it, CALENDAR and CALENDARAUTO
https://msdn.microsoft.com/en-us/library/dn802546.aspx
If that can help!
Well, first thing I would try would be to relate your Date from Forecasting to your Date in YTD. Then, I would see if you can drop down your Date column in your Axis and see if you can choose something like "Day". Is the format of your date something like:
1/30/2016
?
My X-axis is set to "Continuous" and "Linear" if that makes a difference.
@elatreille Your screen shot is very tiny, but from what I can tell, it looks like you are using measures in your graph, can you post the formulas to your measures? My guess is that therein lies the problem.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
105 | |
87 | |
73 | |
66 |
User | Count |
---|---|
122 | |
112 | |
98 | |
79 | |
72 |