Power BI: Hiding future dates for calculations in DAX
Вставка
- Опубліковано 2 жов 2024
- Marco Russo joins Patrick to discuss how to hide future dates for calculations in DAX. Marco walks through Power BI Desktop with some examples that work and don't work.
Hiding future dates for calculations in DAX
www.sqlbi.com/...
********
LET'S CONNECT!
********
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
Check out our Tools page - guyinacube.com...
Thanks Guys! This video totally made me look awesome! Using with Cumulative Budget and Revenue for Current Month.
Dates with Revenue COLUMN =
'Date'[Date]
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.
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.
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] )
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
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]=
Good one Mateus!
I wrote the formula as below and it worked out well.
ps. I am not an IT guy, I am an accountant.
Sales YTD NonVisibleMine =
CALCULATE (
TOTALYTD([Sales Amount],'Date'[Date]),
FILTER(VALUES('Date'[Date]), 'Date'[Date]
This is like when your favourite TV shows decide to do a crossover :D
Ahahhahahahahahahhaha you won
What an elegant simple solution to a common problem!! This can be applied broadly for so many cases as well, thanks for the video!
What a great and simple solution !!
Thanks!!!
Please do WoW dynamic date range with between slicer shows current week(selected date range) and the previous week (of selected date range )
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.
Putting it in report filter is easier, worked for me as well. :D
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
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 ;-)
That works but also means that it might affect measures that you don't want to be affected, doing it in the measure means that it's contained to the measures where you want to apply the logic. Otherwise you'd have to remember to use an ALL() on the date table to counter the report level filter.
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!
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 ?
Always used the IF wrapper, I guess I'll still go ahead with that one :)
If you can't add a new column in your data model, would this technique work using a virtual table?
This just came to my rescue. Thank you, Patrick. What an immense enrichment your channel is!
Another great post, thank you. Looking forward to seeing you guys and Adam at the Manchester PBI User Group this month 👍📊.
I create a 1 row table with the current date index, then filter the YTD measures to have a date index
The concept is very similar - the additional column we create has a content based on a simple DAX query instead of being just
Superb!! Bam🌀
Love this tip, been struggling with this. PS: Is the accent of the gentleman Italian?
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
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. 🙂
In the video we show what is one of the problem using the CALENDAR without complete years - the DAX time intelligence functions are not guaranteed to work in that scenario, I just shown one example but there are many others. If you *don't use* time intelligence functions such as DATESYTD, DATEADD, SAMEPERIODLASTYEAR,... because you create your own filters, then of course that is another option.
Be careful about using time intelligence functions with such a date table, there are cases where the results are not what you might expect. The video has one example of the problems you might have.
Dax "new measure" sucks it´s really hard to learn i prefer to learn Python and create webapp to solve this.
DAX and how the engine works does have a learning curve to it. It is a very powerful expression language though.
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
)
)
It works. You have to have a RETURN after declaring VARs.
another great video !!
Thanks Miguel! This was a fun one to do. Always love hanging out with Marco.
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 )
)
)
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.
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
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?
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.
I have been using report filters, to filter the dates out where sales is null
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 :-)
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.
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.
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
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]) ) )
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.
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
Hi Patrick is there any chance to make Prior week sales video if week number 52 or 53
Nice and elegant, had to extend it for my use case, but it's always good to know this is the "approved" approach :)
great video! can we also prevent future dates in a date slicer under the same situation?
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?
Hola Hilda,
I am not sure if I understood your question, but assuming you want to change the date format from a date with time "01/01/2001 00:00:00 to date as "01/01/2000" for example, then it is simple.
On the Home ribbon click on Edit Queries, then the Power Query Editor will be open, go to the table where you have this date you want to change, the date column has a small calendar icon, click on it and a list will come up, then select Date. After that click and close and Apply.
Let me know how it went.
;-)
Great! That's gave me ideas for other stuffs. See you guys in Manchester in a few days.
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?
That would be difficult. It's best to do that activity directly on the model.
Such a great neat solution
Hey Marco and Patrick , what if I use IS BLANK() in Sales YTD visible measure
I use >0, that works too
why do you need calculatetable instead of FILTER(Calendar,calendar[column]=“true”)?
Performance!
Performance!
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
Such a function doesn't exist in DAX. You should look at how to prepare the data in advance. Maybe in Power Query you can invoke some translation service.
Thanks very much...
You can use isblank function to check if there are sales in selected perid, rather than creating a new column
It is one of the technique described in the article. However, it could slow down complex reports, whereas the additional column keeps good performance.
The article shows the limitations you have with such approach - look at how year-over-year is calculated that way.
Marco, couldn't i just do it like bellow?
Sales YTD =
IF( Sales Amount > 0
Calculate(
Sales Amount;
DATESYTD('Dates'[Date]
)
)
The main issue is performance - there are other scenarios where it could be not enough, the complete solution based on DAX only is described in the article (main challenge is how to correctly compute comparisons).
The article shows the limitations you have with such approach - look at how year-over-year is calculated that way.
Thank you so much!!!!!
Why not put the new column in the report level filter?
You can, but if you publish the model and you connect from other reports, you have to repeat that in any other report.
@@marcorusso7472 Thanks Marco, and thanks for sharing your knowledge. I love your books.
Great both !!!
Thx
Measure approach doesn't work for slicers itselfs. Setting relationship between fact and date table to bi-directional is most elegant imho.
You can use it if you only have one fact table - two or more fact tables, problem. Add one fact table in the future and you have a big problem if you don't want to break existing reports. Be very careful in adding bi-directional filters to the model!
@@marcorusso7472 I see where you coming from. So the big question remains how to remove future dates from date or month slicers?
@@Baldur1005 a page level or report level filter works well in that case. Unfortunately we have to wait that Microsoft supports visual level filters to slicers, so we can use that without enabling the bidirectional filter - which cannot be used anyway if you connect the Date table to two or more tables.
@@marcorusso7472 Thanks Marco. I have read Definitive Guide to DAX and now finishing modeling book. Great stuff and I wish I could swoich and start first with modeling book. DAX Guide was just too heavy for beginner. I wish you could do something similar but with exercise/practice. But love your work anyway. You're The Man.
@@Baldur1005 Thanks!