Allow Mark Date with airSlate SignNow

Add mark date

- Ciao friends! In this video I want to explain you why you need to mark the date table as a date table. Let's start looking at the example I prepared for you. I have a model that contains two tables, sales and date. It actually contains multiple tables, but we are only focusing on these two tables. And I built a report that contains the year, the sales amount, and the YTD of sales. YTD is computing a simple year-to-date using CALCULATE and then the DATESYTD function so it computes CALCULATE sales amount with the DATESYTD over the date table. Everything is working just fine and I did my homework right. If we look at the date table, it is already marked as a date table. If I go to date table settings, you see that it is using the date column. Everything is working fine, but why did I have to mark the date table as a date table? Indeed, if I remove the marking, so if I just uncheck mark as date table, Power BI complains and raises a warning, but I just click on OK, and you see that everything is working just fine, exactly as it was before. The thing is, my code actually should not work. And I want you to understand the reason why it is needed to mark the date table as such. Right now the relationship between sales and date is based on the order date column, which is a date. What happens if instead of using the order date, I use the order date key? So I can just double-click on my relationship, and instead of using the order date, which is a date, I use order date key which is an integer, and of course I also need to use an integer on the other side of the relationship. I just click OK, go back to my report and you see that right now Dates YTD is no longer computing the correct number. The number is the same as the sales amount. So the same code was working earlier, it is no longer working now. Why? Well, to understand that we need to look at the code and understand exactly what is happening with the filter context. Focus on a cell, for example, March. In March, we have a filter context that contains March and then 2007. CALCULATE wakes up, and it applies a further filter using DATESYTD. Now, DATESYTD returns dates, columns of date, date lineage that contains all the dates between the first of January and the last date visible in March. At the end, CALCULATE will apply the new filter, eventually overriding outer filters, but the outer filters are on the month and on the year, they are not on the date column. Therefore, the new filter will be added without removing the previous filters. That is why at the end, you end up with three filters in the filter context, the year, the month, and the dates, and the year and month are more restrictive than the dates. Therefore, it is like our filter has no effect. What we want to do is remove all the filters and replace them with a new filter on the date, which actually happens if I insert a new modifier at the end that does a REMOVEFILTERS, REMOVEFILTERS on the date, adding REMOVEFILTERS on the date makes the code work. So why REMOVEFILTERS was not needed earlier? The reason is, that earlier the relationship between the two tables was based on a date column. Whenever a relationship is based on a date, the engine automatically adds REMOVEFILTERS whenever you replace the filter on the column that is used to link the two tables, and that happens automatically if the relationship is based on a date. If on the other hand, like in my example, the relationship is based on an integer, this REMOVEFILTERS is not added. It will be added if you mark the table as a date table. To demonstrate that, let me remove this REMOVEFILTERS, so the code will no longer work. And we have only the sales amount. But now I can go to Table Tools, mark as a date table, and mark it as a date table. The engine asks me which columns contains the date. So whenever I will change the filter context on... I'm doing that on sales. I should do that on date, okay. Mark the date as a date table. Let me go for date. Whenever you change the filter context on the date column in the date table, the engine will automatically add REMOVEFILTERS and clicking OK makes the code work again. There are further details to learn about mark as date table, but this is the main topic and the reason why it is a best practice to always use... mark as date table all of the date tables which are in your model. If you're interested in going further, go at sqlbi.com, you will find the full articles, the demo file, and a lot of content about DAX. Enjoy DAX.