Answering questions: running total and last dates within selection - Unplugged #38

ciao friends and welcome to a new unplugged video from sql bi in this video i want to answer a couple of questions that appeared on one of the forums of the articles that we previously published the article is about how to select how to make a single selection with a slicer and have a visual like a chart that shows the last six month prior to the selection that you made with the slicer and there are a couple of interesting questions the one one is how to compute a running total instead of just the sales and the other one is how to compute the sales of only the last day of the six previous month last day with sales so let's take first a look at the question then we do a very quick recap of the content of the article because we need to get acquainted with the data model and the tax code and then as we always do with the unplugged we start reasoning on top of the question and then hopefully we finally solve them let's get started now here is the article let me go at the beginning the article is about how to show the previous six months of data from a single slicer selection and the idea is that you have a slicer here with the slicer useless july 2007 but then in the chart you see the sales of the last previous six month i already have downloaded the file so we can take we can take a look at that with power bi you see that if i make a selection for july i see july 2007 june may april so the selection happens with a single date here with a slicer but here i show six months and if i go on december of course i have the sixth previous month the six month before december now we have a couple of questions here it's already sorted by newest let's look at them the first one is carl perkins that says is there a way to use this method to return a running total for the previous period so instead of seeing like here the sales of each individual month he wants to compute a running total so on december i need to show the data starting from july and going to december i guess this is a running total so we start from a value and we make it increase and then there is also another question that is worth looking at that says i have the same request how to display the last three months ops okay let's start again the last three months end date for which the r sales so if they if you select ah if you select one day like the 20 of november 2021 you want to see the 30 of october the 29th of september and the 30th of august this 29 is interesting because we want to show the last date with sales not the last day of the month but we will play a bit with the code trying to find a solution so let's start actually it's good let me see where do we start let's start with the run in total and let me recap briefly how this code works so the final goal is to be able to build this visual that shows the last six month now because the selection happens here on the day table in order to show six months before the land the before december 2007 we need another table and that's what we did here we have sales we have data and then we created another table previous data previous date is used as the axis here and let me overheat select the user then go to calendar year month and you see that on the axis of the chart we don't have the previous date calendar year month so the selection happens with the slicer we use the information from the slicer in order to make a selection of six months out of the previous date and then previous date is used here the entire solution is built with the calculation group that we called previous period and previous period is selected here to be the previous six month you see that we have the selection in the filter of this visual only let me take a look a quick look at the code it's a calculation group so we need to use a tabular editor in order to look at the code let's wait for tabular editor to open here it is okay previous period is a calculation group that has one calculation item previous six month and previous six months takes a has a variable to indicate that we are interested in previous six months it takes at the last date selected with the slicer then let's format the code it takes a no this way it takes the previous six month in previous days and it takes the previous six months in the previous day table and then it computes the selected measure getting rid of any filter from the data and using previous dates as a filter activating the relationship between date and date so if we look at the data model again you see that previous data has a relationship but this relationship is kept inactive we activate this relationship we place a filter here on previous date and as a consequence date will filter only the previous six months but because we use keep filters where is my tabular editor because we use keep filter the visual actually already has a selection on an individual month and this results in a single month being selected that goes to the end result of having here the sales of a month now what we want to obtain is something different we want to compute a run in total so the idea is the same but what we need to do is instead of keeping the filter on the mount so keeping this filter for october and creating a filter that filters six months and then intersecting them what we want to do in october is in october generate a filter that filters all the dates starting from july and going to october so the algorithm will be something like take the end of october as a date and then take the previous six months start no from the beginning we first select the first six months from december to july then when we are in october we take the last day of october and we reduce the selection to be only from july to october so it's actually the first six months but we need to filter to make a filter so that it replaces so that yes it goes from july to october but we also need to take into account the fact that we do have a filter here so if i just replace the filter that will show the value everywhere so we will need to blank those values in some way anyway that's an unplugged so we need to create the code from scratch let me copy preview six month so we copy this we paste it we copy we paste it now we have preview six month one that we call running total six month now previous dates retrieves the previous six months the reference date is the maximum date date out of these previous dates we want to retrieve actually the entire set that happens to be before the max date date but max day date is already is in previous date so that is the reference date in date let me create another variable reference previous date which is at the maximum of previous date date now max of previous day date will be the end of august the end of september the end of october and in previous days we are actually interested in all the dates in the previous six months but we can add a filter here making sure that date date the previous date date is less or equal than the reference previous date at this point in previous dates i will have not the previous six month from the last day date but the previous six months starting from the reference previous date and i'm also using reference date so the two are working together but if i use if i still keep it as it is i don't think anything is gonna change because i need to get rid of this filter i want to get rid of the filter but let me save this let's refresh now what i think is that if i now use here not the previous six months but the running total six month the numbers are the same now it's not easy to see the result just by looking at the chart so let me make a copy of this and we transform this into a matrix no become a matrix my friend [Music] and we also need to change the font to something larger like 20 points oh my god i need to change the values of the column headers to 20 points and somewhere i should also have the row headers row headers to 20 points much better so if i use a running total c922 and if i use a previous six month i see 92 nothing is gone nothing is changing because the filter on july is still active and the filter of august is still active i need to get rid of this filter but i still do not want to show the value everywhere so if i remove these skip filters and i just use previous dates that actually should be enough because now previous dates overrides the filter uh i don't know let me see what happens i see july i start to see data from july because that's december if i select january 2008 i see august that is fine but i also show data later now the reason is and let me see the value for july is the number and then that number grows so the running total is working it's computing the correct value but you see it shows data also in the future which is kind of unwanted because when i'm here in september what happens is that i filter out of the six months before december i filter all the periods that are before the maximum previous the reference date now the reference in the previous date and that computes the run in total but when i'm here in let's say march 2008 that is after december 2007 march 2008 is stored here in the reference date the reference previous date is march 2008. no the reference date is december the reference previous date is march 2008 and i'm filtering out of the sixth month before december the dates that are before the reference previous day so that is not working but actually what i can do is get rid of just not compute the reference previous data as the maximum previous date but stop at the reference date so i can take the minimum of the maximum previous date or reference date so if it happens that reference date happens to be before the last date that reference previous date will go no later than december 2007 let me save this did it save it it did but that didn't work because i really need to blank out the value the value is actually computed so despite looking smart this didn't work now what is the smartest way of doing that well actually what i can do is just can just blank out the value so if the reference previous date is less than less or equal then the reference date that is not going to work either let me try that checks if the values before than the reference date then show something otherwise blank it out now actually it is working already so if the reference previous data that is october is less than december 2007 then it shows the value and that shows 9218 so it's showing a running total a running total of the margin for whatever reason and that should work also here you see that now this shows a running total with uh the when i select instead of previous period i select here run in total 6 month and doesn't look too bad so i can already use this as an answer to the code now let's go for the next one let me recap the next one the next one is that this guy want to show the last three months and dates and not only the last three months but the last three months and dates that requires making code which is a bit more intricate now there are a couple of ways of solving the problem the first one is that to reduce the content of previous data only the end of month if i get rid of in previous date of all the dates except at the end of the month then the filter on previous date will always fit at the end of the month in the previous date and that is already going to work of course doing that will mess up the other calculation but uh but i think it's worth looking at it very very quickly so when i select december 2007 i want to see the end of december the end of november the end of october only the end of the day and we are going to do another calculation item let's start from let's copy previous six no actually i can use previous six months with the filter so let me go back here on previous six months that is showing a value if i reduce the content of previous day to only the end of the period that is going to work so instead of creating previous date as date i just filled the data where date date is the end of the month of day date so i retrieve only the dates that happen to be the end of the month and it doesn't work too few arguments were passed to the end of month the next or the number of months to go ahead i don't want to go ahead of any month so i just bought zero now by doing that if i made it correctly now previous data let's sort it ascending previous date contains only the end of each month and when the filter on previous date happens what you see here is only the data at the end of the month that doesn't actually solve the problem because the question was not how to show only the last end of month days but the last end of mass for which there are sales so if it happens that in a month i do not have sales at the end of the month my code is not work not gonna work therefore i need to make it a bit more complex let's go back here and restore the right scenario okay first of all we need to to find a good test case so i need to find a month for which we do not have sales at the end of the month and i don't know where this month is so let me search it with dax studio we run we basically write a query where we search for a month where sales are not at the end of the month so we start by summarizing sales by date year month do we have a year month date calendar year month so now we have january february march and so on let's take the calendar year month number 2007.01 then we add a couple of columns so one is let's call it end of month which is calculated calculate max of date data that is the end of the month and then the end of sales end of sales where i retrieve the max and not update but of sales order date so this is the end of the month this is the end of date where we do have sales and i'm interested only in the rows where end of month is not the same as end of sales okay so we have a couple of examples november 2007 then august 2008 we have sales on the 31 of october of august sorry the end of august is the 31 but the end of season the 30. so that is a good example if i select september 2008 i should have my use case so i need to go to september let's go for october 2008 i'm seeing data for august but i don't want to see the data of august i want to see the data at the end of august so i need to change my calculation group so let's create another calculation group that will restrict the sales only to the last day of the month let's start with start from previous six months i copied i pasted it and that becomes the previous six end of month so what should i do well let's first do a check if out of dates imperial out of previous date i only select the end of the month i would expect my report to hide september because we to hide august because we know that in august we do not have sales at the end of the month so if i apply a filter here using the same technique we used previously so i want previous day date to be the end of month of previous day date come zero so that previous days becomes previous end of month let's call it previous end of month if i now apply this calculation item and i'm doing it right i should have august disappear we save it we need to refresh this and then on this visual we select the previous six end of mountain and that is good you see that august now disappeared because in my filter here i'm selecting the last only the end of the month but it is not actually what i want to do i want to retrieve the last date from the sales table so the previous end of month that needs to be a filter that's that needs to be completely different so i have the reference date that is maximum day date let's create previous end of month and i follow the same pattern i did earlier i summarize sales by date calendar year month number and then i add the column to this that is the last date with sales so i create a new column end of month last date with sales which is calculate max of sales order date now at this point in the fees table i have by year month number the last date with sales i'm not enter and i have actually the calendar year month number and the last date we say i only want the last date with sales so we add sell it columns and we want a new a last date with sales so last using this last date with sales which is last date with sales actually this calculation need to happen for the last previous month so let me get rid of this part and these dates in period need to be inside a calculator calculate all fees in dating period so let me check i'm retrieving the date these days in period need to be now on date date so i take the reference date that is the last day date i start from the last date i go back six months and for six months i retrieve the last date with sales and that generates a table previews end of mouth that tabular editor says is a scalar because i'm using calculate that should be calculate table that is much better now it's a table with one column that is a last date the last date with sales now i have in previous end of month the last date with sales that has no lineage because i didn't use i computing it with max sales so i need a three tasks we can do that in a different variable previews end of mouth with lineage and i used retards to place a filter we can place the filter well let's place a filter on previous date so we treat as previous end of month as previous date date so now is a daytime a column daytime with the last date with the lineage of previous date and then i think it's enough to use this variable here we remove a filter from date we keep the filter we keep filled that's because we want to use the current month and we activate the relationship i need i mismatch the variable that is previous end of month with lineage i would say that this should work totally not sure about that let me save it go back here the previous six end of month i have no idea whether it's working we can look at the individual numbers if i select the previous six month that shows for august 9 5 2 if i take the previous six end of month for it shows august 18 000 that is likely to be the sales of the last date for august we can actually add the date just to check that we have a good number you see for august oh it's showing all the dates in august why that i i don't need to use date date i need to use previous date date much better for august it shows the 31 of august oh because i needed to choose here december 2000 december 2008 for august it shows the 30th of august for september the 30th of september for october the 31 of october and in august it shows 23 000 which is much less than the original value sorry the original value that would be there if i use the previous six months actually i can get rid of the margin and take the previous period place it on the columns and then get rid of this filter so previous six end of month show a value only for the 31 which is the same value here that is correct but if i remove the date i have the previous week's end of month the previous six months and the running total out of six months so that looks like we solved it it's now time to go back to the forum answer the question provide the code and then time to edit the unplugged i hope you liked this video as it always happened with unplugged videos we do not exactly know what's gonna happen in this case we had two quite simple questions both were related to how to change the filter context and we were lucky because the code in the calculation group was nicely written so changing the filter context was not that hard remember working with dax is always a matter of understanding how to move the filter context the right way you start from a filter and then you play with the filter context going back and forth until you find a suitable filter that computes the right value that is not magic tax is only about understanding the raw context the fitter context context transition and then learning how to use the tools and your brain in order to solve problems i hope you like the video and enjoy dax [Music]

Channel: SQLBI

Views: 2,937

Keywords:

Id: Ljc10BJSdQU

Length: 33min 34sec (2014 seconds)

Published: Sat Dec 04 2021