Power BI: Hiding future dates for calculations in DAX 

Great tip, I've used this approach in the past YTD Sales to Last Actual Sale = VAR DateofLastActualSale = CALCULATE ( MAX ( SalesTable[Month End Date] ), ALL ( SalesTable ) ) VAR RESULT = IF ( MAX ( 'Calendar'[Date] )

Thanks Guys! This video totally made me look awesome! Using with Cumulative Budget and Revenue for Current Month. Dates with Revenue COLUMN = 'Date'[Date]

This just came to my rescue. Thank you, Patrick. What an immense enrichment your channel is!

What an elegant simple solution to a common problem!! This can be applied broadly for so many cases as well, thanks for the video!

Amazing solution. Only change I'll probably make is adding that column in Power Query since that is where my calendar table is generated. Love this stuff!

Another great post, thank you. Looking forward to seeing you guys and Adam at the Manchester PBI User Group this month 👍📊.

I have used the column approach and put it under report filter, it works for all my measures, the good thing about that is when sales get added daily, my date filter period slicer automatically gets updated showing the users the latest sales data. Marco’s solution to add it in measure is pretty cool too.

You are magnificent merging both YT channel of yours representing a mutual community to wider scale. I feel like two of my friends are friends also.

This is a great video. "Guy in a Cube" please do a video on preparing DAX for generating last n months report when you select a date

Great! That's gave me ideas for other stuffs. See you guys in Manchester in a few days.

Hi Patrick , great video. Before I used to do it like this : Sumx(values('date'[date]),if[Sales Amount]>0 , calculate ([sales Amount] , DATEYTD('date'[date]) ) )

Nice and elegant, had to extend it for my use case, but it's always good to know this is the "approved" approach :)

What a great and simple solution !! Thanks!!!

It's good to know that I'm already following the best practices :) the only thing I do different is that I move the Boolean date filter to the 'report filters' and filter only true values, since I don't want to show this anywhere in the end.

This is like when your favourite TV shows decide to do a crossover :D

Such a great neat solution

Hi.. want to thank you for this. Wonderful. I now fully understand how to create my own measures for this without touchig the date table. Wondeful.

I do the calc column approach, but create a CALCULATED measure in the column that brings sales by day back to each row in my time table. Then I wrap that in an IF then and return “Flag” for days w sales. It works for what I need it for, but this approach in this vid includes every day prior to the max order date. And I would guess it has better performance. Thanks for the video.

Thanks Patrick for one more nice video. I have a request for Marco, could you please share any video on detailed explanation on DAX Studio. Indeed it's a great tool but very less videos are available so far. Your guidance will really help to use the tool more efficiently. Regards

Thank you so much!!!!!

Always used the IF wrapper, I guess I'll still go ahead with that one :)

Thanks very much...

Superb!! Bam🌀

another great video !!

Great both !!!

I actually did this today but with a different approach. I bring my date table in through PQ because my company uses a week based calendar and it's just easier to bring in a csv file with all the columns. I use the same file for the date table in several reports, it runs from October 2013 to September 2021 (for now). In PQ I create two queries using the blank query option. In the let statement I use either a hard coded date using #date(yyyy, mm, dd) or using M functions to calculate a date such as using Date.From(DateTime.LocalNow()) to get the current date. I name the queries start and end dates as appropriate. Then I change them from table to list. Then I create two parameters, one for the start and one for the end. In the parameter creation dialog I change the source to query and pick the start or stop list query as appropriate and use either the hard coded date as the current value or whichever date is appropriate. After creating the parameters I go to my date table in PQ, choose the date column, and apply a custom filter. The parameters are available as options as part of the logic. It's a simple matter then of creating the filter to pick dates on or after your start date and on or before the end date. I know it sounds like a convoluted process but it took me way longer to type this than it did to actually do it.

great video! can we also prevent future dates in a date slicer under the same situation?

I would just build a date table using CALENDAR( MIN(MyTable[MyDate]), MAX(MyTable[MyDate])). This would work for this single fact table example. However, I really like this solution for bringing multiple tables with different dates ranges together. Thank you for this solution. I will be implementing this in some reports in the future. 🙂

I use vars in DAX. Sameperiod or YTD is not working for me as i need users to be able to select year so: i use Max or SelectedValue to see what date is being displayed and calculate date range. Then you can use All(Calendar) to ignore filters user selected in calendar table and calculate range using var. Since vars are defined before All(Calendar) is being used, dates will aggregate based on year selected in slicer, or displayed on table/chart. When date limit is needed i add 3rd var with Calculate(Max(Sales[Date]),All(Sales)) and add condition for calendar to be lower than mentioned var. Sample: YTD = var vMin = Date(Max(Year(Sales[Date])),1,1) var vMax = Max(Sales[Date]) Return Calculate([Sales],Filter(All(Calendar),And(Calendar[Date]>=vMin,Calendar[Date]==vMin,Calendar[Date]=

Love this tip, been struggling with this. PS: Is the accent of the gentleman Italian?

Please do WoW dynamic date range with between slicer shows current week(selected date range) and the previous week (of selected date range )

I create a 1 row table with the current date index, then filter the YTD measures to have a date index

I have column offsets in my calendar table thats applied to the week and month, where anything negative is in the past, anything positive is in the future, and 0 being the current month/week.

Hi Patrick is there any chance to make Prior week sales video if week number 52 or 53

For the Filter field created with values as True/False, can we not apply it as a visual level filter? This way you do not have to worry about creating the Calculatetable.

I know this is an old thread but this might help someone. I've achieved this without having to add the extra column by directly filtering the dates available in the Calculate function. Using INTERSECT with a Max date from the data table I'm able to restrict the dates from the calendar table that are used. Measure = VAR DataMaxDate = CALCULATE ( MAX ( 'Data'[Date] ), ALL ( 'Data' ) ) RETURN CALCULATE ( Your Calculation goes here , INTERSECT ( VALUES ( 'Calendar'[Date] ), DATESBETWEEN ( 'Calendar'[Date], BLANK (), DataMaxDate ) ) )

Great video - It doesn't quite work for me though, as I'm connecting live to a SSAS tabular model and can only work with measures. Is there a way to create a measure to flag each date of the date table as either in the future/not in the future?

You can use isblank function to check if there are sales in selected perid, rather than creating a new column

Another approach would be to to apply the true-filter on the report level filter. Then everything would work like before. But your approach was a little more elegant ;-)

Great video, thank you! Any idea how to apply the same trick but for dates in a bar chart in the x-axis. I need to display all dates including ones without values but within the range of min and max dates in my table. I marked x-axis as categorical, ticked display items with no values and all dates are displayed, however, these dates are not dynamically adjusted when I change the month filter in the slicer and are rather static depending on the range in the whole dataset. Its only dynamic if the axis is continuous, but not all dates are displayed (values are there but many labels are missing) Any ideas?

Thanks. But after creating 'DatesWithSlaes' column in Dates table, and using the same in my measure, the sub total is not giving me the YTD total. It is giving me only the current month total. Please let me know how to fix this ?

Great Video!! but I have a little issue because my date has timestamp, so it's only recongizing those values that have timestamp "00:00:00" is there a way to use this function without creating an additional date column with no time or transforming my date/time column to just date?

Hey Marco and Patrick , what if I use IS BLANK() in Sales YTD visible measure

I have been using report filters, to filter the dates out where sales is null

Hey Patrick , Do we have any dax function to convert text column data which is of different language and i want to convert it in to english

hi Patrick, i started to did the same exemple(CALCULATE([Measure];DATESYTD('TABLE DES DATES'[Date])) but the matrix showed not all the month as in the video it show only until the last transaction and i checked the number they are correct! so did they fix the visualisation ? Do we not need to create that column any more ? thanks

If you can't add a new column in your data model, would this technique work using a virtual table?

Thx

is it possible to add hidden objects in powerbi as this Boolean object that need to be set? I think it's also great for documenting the process , not to be lazy , but efficient :-)

What if you define your date table using calendar, min and max from your sales table? We would get just the dates with sales for all the calculations

Patrick, can you please do a video where we get the last date from 3 columns in the same table each having dates, but we ignore future dates. Basically if one column had 1/5/19, another column had 5/1/21, and another had 1/6/15. I want to pull the latest date but not ones that are in the future. This would be very helpful

Why you are not using the max and the min date of your sales table when you create your date table ? This is what I am using

why do you need calculatetable instead of FILTER(Calendar,calendar[column]=“true”)?

Why not put the new column in the report level filter?

Marco, couldn't i just do it like bellow? Sales YTD = IF( Sales Amount > 0 Calculate( Sales Amount; DATESYTD('Dates'[Date] ) )

IS this possible to do with the Generate Auto Calendar DAX Table? would you put the code under a VAR line before RETURN ROW? I'm super new at DAX. DAXCALENDARTBL = VAR BaseCalendar = CALENDARAUTO ( 12 ) RETURN GENERATE ( BaseCalendar, VAR BaseDate = [Date] VAR YearDate = YEAR ( BaseDate ) VAR MonthNumber = MONTH ( BaseDate ) VAR MonthName = FORMAT ( BaseDate, "mmmm" ) VAR YearMonthName = FORMAT ( BaseDate, "mmm yy" ) VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1 RETURN ROW ( "Day", BaseDate, "Year", YearDate, "Month Number", MonthNumber, "Month", MonthName, "Year Month Number", YearMonthNumber, "Year Month", YearMonthName ) )

Dax "new measure" sucks it´s really hard to learn i prefer to learn Python and create webapp to solve this.

Measure approach doesn't work for slicers itselfs. Setting relationship between fact and date table to bi-directional is most elegant imho.