Great video albeit with one oversight: the NOW() function uses the "local machine" time - you're a happy camper sitting in Australia (GMT+10) this works perfectly in Power BI Desktop. The moment you deploy this report to the Power BI SERVICE (which is UTC time) it won't work and you're always a day behind. Worth mentioning - I always have to create 2 columns to cater for both PBI Desktop and the service.
Nice job. There is a more "efficient" way to do this. If you have a proper Date table, then you can simply add the Date column to any filter, change the filter type to "Relative date filtering", then select "is in this" (or "This") and then "Day" (or Days).
Honestly overlooked that feature. Definitely faster and more efficient. You know me, I'm not lazy just efficient. Video on Relative Date slicer coming soon!
@@GuyInACube Hi Patrick, this is great, but my users want the latest Date on Slicer selected when the data is refreshed. And still be able to goto other days in the Slicer later. How do I do that?
Great video, Patrick. Any tips on what most people is asking about: "set default on date slicer (could be today, last 7 days, last 3 months, etc) while still allowing users to select any other period range"? Is this something that can be accomplished with two date slicers and bookmarks to display one or another at one time? Thanks, mate
This is definitely a very good QoL feature for reports. For my company I had to make other "current time" filters (current year, month, week, etc.), and - as you said in the video - you can do it in many ways, I did it in Power Query.
Thank you Patrick. I need to try this with my data as i need to send daily report to my office through mail. If this works then it is a great help by you. Let me check and revert to you.
Me too. This solution is interesting, although by that way user using published reports won´t be able to change the filter, right? On my report, there is a filter visual component for years selection, and what I need to do is keep current year selected, but allow the users to choose another year. Does anybody have a tip for that?
Thanks again for sharing. What's the use case for this? Is there a benefit to choose this route over the "Relative Date Filtering" ? Appreciate all the work you guys do.
Hi, Another approach is using Relative Date Filter : Add the date in the Page level filter and select Filter Type = 'Relative Date Filtering' and select is the last 1 day ..it will always show the today's data..
Wow... That's dope. I'm just learning Power BI and have recently subscribed to your channel and like your videos. When you filter by today's date, how can your client/me see last week to today date display on your filter dynamically?
This solution can also apply to make the YTD functionality work with dynamic YEARENDDATE. For those that does not know it, the TOTALYTD function in DAX does no accept dynamic end of date. If you just alter a bit the code provided in the video in the istoday field and add a slight different if statement {} and then add a report/page level filter BOOM. You have it. Dynamically close all the YTD functions to the current date.
Nice Patrick! Quick question. I have a product table with a Created and Delivery calendar. When I create your calendar how can I connect it to Delivery information. (When the report opens it should show all of todays delivery). Thank you :)
Hi Patrick, nice video, but this locks the user out of being able to choose another date. I'd like to be able to use the single date chooser from your other video and have it default to yesterday. Essentially I'm creating a dashboard where people can see what happened yesterday, but if its Monday they might like to go back and see what happened on Friday/Saturday or another date
Hi Patrick, Great vid as always. I run a "is current day" filter in my power query on direct query. This is great for today but when it goes over to tomorrow it gets stuck and only shows yesterday. If I refresh the data in Desktop and re-publish it works again. Do you have any way around that so I don't have to publish the dataset daily? Anyone had trouble with this?
Put your date in the filter pane --> Filter type 'Relative date' --> is in the last x week. Month = 30 or 31 days (e.g. June 3rd to July 2nd), Calendar month = everything that has the same month name (e.g. June). Similar logic applies to Week vs Calendar Week and Year vs Calendar Year. This option is also available for your slicers on the page itself. The logic shown in the above RUclips video is also nice but I think better applied when you take a broader range which is applied to multiple pages. Because then you set the value of - say - 3 months once, and on all pages it will use 3 months. Then when you change it to 4 months in your column definition, all pages change instead of adjusting all the filters.
Thanks, but I would like to dynamically filter by todays date (or some date based on a logic) while giving my customers option to CHANGE the filter/slicer. This feature is really missing in PowerBI
Thanks, it could be a workaround but I need my customers to be able to select dates, not "days ago". The default member based on MDX was a feature in a Panorama Software 12 years ago.... When will we see it in PowerBI?
Thanks for the video! Is there a way to do this same thing but dynamically select the previous day rather than today's date? I've tried a couple of things with DateAdd() and DatesBetween() but they dont seem to want to cooperate with Now(). Thanks!
I think you can just subtract 1 day. I used the TODAY() function (VAR yesterday = TODAY()-1). If you then go back to the video: you probably need to format the result to be able to compare.
Heeey, Patrick! W'up? Thanks in advance for the many lessons and also to this support below: So, I am developing a dashboard in Power BI and I would like to demonstrate some KPIs values in 2 formats: one settled from the last month and the other one from any dates I want to select from a slicer. That's the struggle, how can I "hedge" the first KPIs for only show the last month and at the same let the others free to select the period I want?
Thank you Patrick, Great and informative tutorial. A small question. We have future dates as well in the date list and based on a requirement that I have the date slicer should be in between format and also by default the date range should be in the current year (the first day of the current year to today). Can you pls explain a workaround for that ...Thanks a lot in advance...
Nice Tips, IsToday Column by DAX. 😄 I hope the creating IsToday column to New Quick Measure of Power BI, for everyone. and also use for IsThisYear, IsThisQuater, IsThisMonth, IsThisWeek, IsThisHour, IsTodayInOurTimezone etc.
Thanks for another nice video! Would be interesting to see video and your thoughts how to implement periods and period compering. By example last 30 days compare with 30 days before last 30. Is the best way to do it data model and period tables or DAX?
I saw this link and was wondering how it was done. app.powerbi.com/view?r=eyJrIjoiZWZjODA3MzMtMDE0MS00NmRlLWFiY2EtZDQ3ZDU4MGI5OGU4IiwidCI6ImUwOTY3NDNiLWMyM2QtNGVjOS1hZjI4LWViMGY5OTgxMTcwNCIsImMiOjh9
Hi Patrick, Thanks for ur PBI Videos! Is there a way to check, how many people are checking my dashboard in PBI website? Basically, I want to see the count whenever i refresh d data? Thanks!
@Jakub Mazgut no ... I only use date not date time...but the relative date always use UTC time... if. you are in UTC -3 and is 21:00 if you select "this day" in slicer they will show the next day...
gong127 no... the UTC time is the default and only option...see on details os the filter in Microsoft page docs.microsoft.com/en-us/power-bi/desktop-slicer-filter-date-range
That was nice, Thanks. I would like to know if it is possible to set the default selection on a slicer to the most recent month?, and let the users the option to select another month If they want to.
I might sound a bit picky :), but the comment was "dynamic selection of the _latest_ date in a slicer", which necessarily hasn't have to be today. Eg.. you make a quality control once a week (with multiple lines of data) and you want the latest quality control only. Relative date on last week doesn't work, since sometimes you perhaps make one week two controls or perhaps you forget one week.You can get the value of the "latest date" with a measure but you cannot use measures in page/report filtering. Any suggestions?
Hi Patrick(@Guy in a Cube) Thanks for all amazing tutorials. How do we show last networking working day excluding weekend and holiday?I have two table, calendar table and sales table, i want to show the sales of last working dates. Thanks
Thank you for a great tutorial. I've got some problems still, when i follow your instructions they work great but unfortunatelly i can't choose any other value from my slicer. Is this normal behavior or am i doing something wrong?
You cant see any other value in the slicer because you have applied report level filter and it is being applied to each data attribute for it, in that case you need to remove report level filter
A good practice when using TODAY() or NOW() in your DAX: don't! You will likely want to test for different conditions based on what "today" is (1st of the month? A weekend day? etc.) So create a measure for TODAY() and reference it so you can later change what it evaluates to, tricking your code into thinking it's a different day. Ex: MyToday = TODAY(), which can later be changed to MyToday = '2020-02-29' to see what happens on a leap year.
Hi, I want to load data in a table based on Max date for the first time when the report will load, but if user choose another date from date slicer it will load data accordingly. So if user deselect all the dates from the slicer it will load again data based on Max date available in the table. Can I achieve that?
I have data up to NOV 2021 when i am WORKING in 2/Feb/2022. I have created calendar using Min( Fact_Table[Billing-Date]), MAX(Fact_Table[Fisca-lDate]). Now in my slicer i want to show latest Month NOVEMBER dynamically (by NOT selecting manually) & Previous Months. If I get any data like in Dec2021 in the back end, in the slicer i want to see Dec Month whenever i refresh the file. We are using USA fiscal policy. Use DAX ONLY
Could you please help me in writing a DAX formula or a process which will return a value based on the current day of the week and the corresponding week number? Assuming today is Week 1, Friday, I want to add a "Card" visualisation to show "Project01" for employee Name01 (assuming I have the separate "Slicer" filter for the "Employee Name").
Then the next time I open my Power BI, which will be on Monday next week, the same "Card" will give me the Monday value of Week 2; which for instance for "Name01" who will be working to a new project called "Project02". Kinda similar with the one you presented in your video where the data updates based on the current day. I hope my questions make sense. Thank you in advance for the help.
I NEED HELP! PLZZ im trying to do something similar but more specific i guess. im a server and ive been working on an excel file to track my tips and sales percentages tip outs etc, it's very complex and im completely new to excel so it took me like two months. i'm trying to recreate an app that i was using but doesn't track enough details. in this app there are tabs on top for sorting the data yearly, monthly, weekly and biweekly and "all" and then at the bottom you can toggle between all of the data with arrows, between the 2 arrows there are 2 dates based off of the filter (the corresponding dates of the month, the week etc) but youre not limited to just "this month" and "last month", but you can continue to toggle between all months by clicking the arrow. i've tried with slicers and it kind of works but not as efficient or specific as i'd like it to be PLEASE TELL ME if you think that is at all possible on excel
Hi, just joined your channel. :-). Liked your video. Have you created an if statement in power query (not power BI) where a date in an existing column is greater than or equal to “today” ? This would allow me to filter only the records with a date greater than or equal to today. Thoughts ?
How to I structure my dataset to show the week to week progress? Week 1, 3 things done. Week 2, 7 things done. Week 3, 15 things done. I want to show between weeks 1 and 2, that 4 things got done, etc..
Hi Patrick! How can this be tackled in case we are accessing the reports internationally? Since you are using the NOW() function, it will always take the current system’s date. So once the report goes to the service/publish to web, the NOW() function will use the date according to the location. So if the report is updated in one country and is being accessed from another, it might not show the last date correctly. Thanks!
Partrick thanks for the streams. I have a support to ask. I have a data with parts effective out dates I want to know as of date what is the no of years has that part crossed. If i use today function its gona reflect every day. Can you help me out. Thanx
Thank you Patrick. Since Now() gives the service/server date/time, it will work for a country but not for other countries. For instance, in France when it's 01:00 AM, it's 07:00 PM in NYC (one day earlier). Can this solution be adapted to calculate the Now() with offset based on the time zone of the viewer ?
In theory yes: create a date dimension using a view. Create a column containing the week name, and in case it is the current week label it "current week", in all other cases label it with the real week (e.g. CW17,...). If you set the visual filter for the slicer to "current week" on that column and save the report, it will show always the right week when opening and refreshing it.
Hello, I use power bi with tabular server and I just use calculated fields. (I cant work with date fields). I created a GP report . Orders usually record on time . but sometimes orders include turnover but they dont cost, so GP report dont reflect the true GP. It controls once a week so I would like to report it 7 days before bookings. I use relative date but I dont do that, Is there any solution for this. could you suggest a way to do it
Hello Patrick, I need to filter in a specific way and I could not find anything to help me so far, I need to put two graphs on the same page, which show the current year separated by months and last year separated by months also to be able to compare between one year and the other, but I could not filter it that way, could you help me or point me to a link that might?
Hey Patrick, Is it possible to Schedule sending a report with predefined parameters to an email? i'm aware of sending a scheduled report which is simply a snapshot of a report or a Dashboard, but what if i want some filters to be pre-populated before sending that report to a specific email?
It would be great if the relative date filter had the option for today, this month, Plus and Minus ie so you can set it to show a rolling period centtred on the current date. i.e. Today +/- 1 month, find I need to manually work around this so many times, or ois there a simple workaround I have not found. a lot of the time im working in measures so the column date doesn't always work.
I can see having a slicer default to the current date while still allowing the user to select other dates, but I'm not sure why anyone would want to make a slicer show only the current date. Why even display the slicer on the page if it's filtered down to only one choice?
Is there a way to automatically set date sync slicers to default to the last 3 months but still allowing the end user to change the date on the sync slicer?
Esayas Bemnet thank you for the help suggestion. I will go a little deeper into my issue. I have several reports/visuals across multiple tabs. The first tab is a "filter selections" page, it has several sync slicers with a reset button I created that works off bookmarks. (Resets all selections on the sync slicers back to unselected). On the date sync slicer I have switched from between to relative month calendar.....great this works the way I want it to. But, I cannot use the reset button as it switches the date picker from relative to between / my defaulted relative calendar months to zero. There is an option to ignore data and visuals in bookmarks but this affects then the whole filters page which breaks the sync slicers. Ive also tried using the page filter with a relative date and leaving the date sync slicer as a between. If I hit my reset button it also wipes the page filter settings. It feels like im close to many workarounds/options but then hit a brickwall again....
I am using multiple sources on my dashboard to create different charts, I need to use one date which can be used for all sources and all charts. how am I supposed to do that?
Hi Patrick.i need help with TotalYTD function.This function returns value till today but also spreads this value for all future dates of the current year.e.g today is 28 Feb 2020,it will calculate YTD e.g 150 till today but also shows 150 for all future dates of year in the visual.is it a way to limit the YTD only till current date
Great video albeit with one oversight: the NOW() function uses the "local machine" time - you're a happy camper sitting in Australia (GMT+10) this works perfectly in Power BI Desktop. The moment you deploy this report to the Power BI SERVICE (which is UTC time) it won't work and you're always a day behind. Worth mentioning - I always have to create 2 columns to cater for both PBI Desktop and the service.
Love this channel! This helped me solve a major issue with my report! I just had to change the dax a little bit, but it worked all the same!
Nice job.
There is a more "efficient" way to do this. If you have a proper Date table, then you can simply add the Date column to any filter, change the filter type to "Relative date filtering", then select "is in this" (or "This") and then "Day" (or Days).
Honestly overlooked that feature. Definitely faster and more efficient. You know me, I'm not lazy just efficient. Video on Relative Date slicer coming soon!
Guy in a Cube
I’m trying to be efficient too😉
We have something in common, we love ♥️ #PowerBI as it makes us more “EFFICIENT”.
@@GuyInACube Hi Patrick, this is great, but my users want the latest Date on Slicer selected when the data is refreshed. And still be able to goto other days in the Slicer later. How do I do that?
@@samrockx-live8427 this is my question as well. Not sure why a date slicer *wouldn't* update on a data refresh?
Wow. That solved the problem with ease. I recommend you post this to some forums so it can help others more easily!
Great video, Patrick. Any tips on what most people is asking about: "set default on date slicer (could be today, last 7 days, last 3 months, etc) while still allowing users to select any other period range"? Is this something that can be accomplished with two date slicers and bookmarks to display one or another at one time? Thanks, mate
This is definitely a very good QoL feature for reports. For my company I had to make other "current time" filters (current year, month, week, etc.), and - as you said in the video - you can do it in many ways, I did it in Power Query.
আপনাদের ভিডিও দেখে কিছু শেখা হয়ে গেছে
খুব সুন্দর লাগলো দারুণ একটা ভিডিও
এক কথায় বলাজায় অসাধারন
Thank you Patrick. I need to try this with my data as i need to send daily report to my office through mail. If this works then it is a great help by you. Let me check and revert to you.
First i thought this is not what i wanted, but then it was mutch better then the solution i looked for =D Amazing
You can also use keywords like Today and Yesterday in Q&A without having these values in the date dimension. But agree with Patrick's approach.
Thank you Patrick, It's a great silution I'm looking for.
Does anyone know how to make a date slicer always choose the current day (while still being able to chose other days)?
Hi MrAwesome have you figured it out yet?
@@PhoebeeMo I'm also looking for this
@MrAwesome I´m also looking to figure this out, did you get an answer or find out about it??? pls let me know, Thx
Me too. This solution is interesting, although by that way user using published reports won´t be able to change the filter, right?
On my report, there is a filter visual component for years selection, and what I need to do is keep current year selected, but allow the users to choose another year. Does anybody have a tip for that?
@@hugodasilvadasilva6550 Hi Hugo, did you ever find a solution for this issue? I am also running into this situation. Thanks!
Thanks again for sharing. What's the use case for this? Is there a benefit to choose this route over the "Relative Date Filtering" ? Appreciate all the work you guys do.
Honestly overlooked that feature. Great point!
The first thing that came to my mind before watching this video.
How did you put Select a date?
Relative date filtering will over write date to UTC date and that's the messed up thing
A suggestion to post the code below the video. Makes stuff easier to copy. I'm not lazy just really efficient ;).
I love this guy
We love you too! 👊
Hi, Another approach is using Relative Date Filter : Add the date in the Page level filter and select Filter Type = 'Relative Date Filtering' and select is the last 1 day ..it will always show the today's data..
Wow... That's dope. I'm just learning Power BI and have recently subscribed to your channel and like your videos.
When you filter by today's date, how can your client/me see last week to today date display on your filter dynamically?
This is perfect what I'm looking for. Well I want this as default as my current date and when drop-down the slicer I want others date along with All
This solution can also apply to make the YTD functionality work with dynamic YEARENDDATE. For those that does not know it, the TOTALYTD function in DAX does no accept dynamic end of date.
If you just alter a bit the code provided in the video in the istoday field and add a slight different if statement
{}
and then add a report/page level filter BOOM. You have it. Dynamically close all the YTD functions to the current date.
This is cool... thanks. Question for you, is there a way to make the last 4 weeks show based on a single date selection ?
cheating....
Hi Patrick, plz make a video on advance drill down map visual. Something related to gps.
Great information
Nice Patrick! Quick question. I have a product table with a Created and Delivery calendar. When I create your calendar how can I connect it to Delivery information. (When the report opens it should show all of todays delivery). Thank you :)
Hi Patrick, nice video, but this locks the user out of being able to choose another date. I'd like to be able to use the single date chooser from your other video and have it default to yesterday. Essentially I'm creating a dashboard where people can see what happened yesterday, but if its Monday they might like to go back and see what happened on Friday/Saturday or another date
Filter by how many times you've saved my as* Thank you BOSS!!!!!!
Hi Patrick, Great vid as always. I run a "is current day" filter in my power query on direct query. This is great for today but when it goes over to tomorrow it gets stuck and only shows yesterday. If I refresh the data in Desktop and re-publish it works again. Do you have any way around that so I don't have to publish the dataset daily? Anyone had trouble with this?
What about "This Week, Last Week, Other" ?
I have been wracking my brain on this for 2 days! lol
Does anyone solved this? Having the same issues here
ruclips.net/video/pCgK-Ze3nhA/видео.html
Put your date in the filter pane --> Filter type 'Relative date' --> is in the last x week. Month = 30 or 31 days (e.g. June 3rd to July 2nd), Calendar month = everything that has the same month name (e.g. June). Similar logic applies to Week vs Calendar Week and Year vs Calendar Year. This option is also available for your slicers on the page itself.
The logic shown in the above RUclips video is also nice but I think better applied when you take a broader range which is applied to multiple pages. Because then you set the value of - say - 3 months once, and on all pages it will use 3 months. Then when you change it to 4 months in your column definition, all pages change instead of adjusting all the filters.
Thanks Patrick, you guys are awesome 🤘
Thanks, but I would like to dynamically filter by todays date (or some date based on a logic) while giving my customers option to CHANGE the filter/slicer. This feature is really missing in PowerBI
For that, I use text and a datediff, I put some text as Info from "datedif between today and date field" days ago, and put that into a slicer.
Thanks, it could be a workaround but I need my customers to be able to select dates, not "days ago". The default member based on MDX was a feature in a Panorama Software 12 years ago.... When will we see it in PowerBI?
Pretty neat hack!!! thanks a lot!!!
Helpful. Thank you.
Cool 😎 Thx a lot for sharing. Greetings from germany Christoph 👏 👏 👏 👏 👏
Thanks for the video! Is there a way to do this same thing but dynamically select the previous day rather than today's date? I've tried a couple of things with DateAdd() and DatesBetween() but they dont seem to want to cooperate with Now(). Thanks!
I think you can just subtract 1 day. I used the TODAY() function (VAR yesterday = TODAY()-1). If you then go back to the video: you probably need to format the result to be able to compare.
Great job guys. Thanks for sharing your expert knowledge.
Thanks for watching Jonathan!
Thanks patrick!!! Great Tip. Do you know how to do an slicer set dynamically from week 1 to current week?
Heeey, Patrick!
W'up?
Thanks in advance for the many lessons and also to this support below:
So, I am developing a dashboard in Power BI and I would like to demonstrate some KPIs values in 2 formats: one settled from the last month and the other one from any dates I want to select from a slicer.
That's the struggle, how can I "hedge" the first KPIs for only show the last month and at the same let the others free to select the period I want?
Hey Patrick. can you make a video to create dynamic dendogram using power bi
Again.....great tips!
Thank you Patrick, Great and informative tutorial. A small question. We have future dates as well in the date list and based on a requirement that I have the date slicer should be in between format and also by default the date range should be in the current year (the first day of the current year to today). Can you pls explain a workaround for that ...Thanks a lot in advance...
Nice Tips, IsToday Column by DAX. 😄
I hope the creating IsToday column to New Quick Measure of Power BI, for everyone.
and also use for IsThisYear, IsThisQuater, IsThisMonth, IsThisWeek, IsThisHour, IsTodayInOurTimezone etc.
Thanks a lot for value
Thanks for another nice video!
Would be interesting to see video and your thoughts how to implement periods and period compering. By example last 30 days compare with 30 days before last 30. Is the best way to do it data model and period tables or DAX?
Hi Adam and Patrick!
Can you please do a video on how to use MTD, QTD and YTD on a slicer (not as a bookmark)?
Thanks!
I saw this link and was wondering how it was done.
app.powerbi.com/view?r=eyJrIjoiZWZjODA3MzMtMDE0MS00NmRlLWFiY2EtZDQ3ZDU4MGI5OGU4IiwidCI6ImUwOTY3NDNiLWMyM2QtNGVjOS1hZjI4LWViMGY5OTgxMTcwNCIsImMiOjh9
We will get something on the list for that. Thanks!
Nice 😍
Hi Patrick, Thanks for ur PBI Videos! Is there a way to check, how many people are checking my dashboard in PBI website? Basically, I want to see the count whenever i refresh d data? Thanks!
Thank you so much
Its a Good thing because then we use the relative date in a slicer and choose this day they use UTC hour to determinate if is today.
@Jakub Mazgut no ... I only use date not date time...but the relative date always use UTC time... if. you are in UTC -3 and is 21:00 if you select "this day" in slicer they will show the next day...
@Rafael does it work if you changed your regional settings in Power BI?
gong127 no... the UTC time is the default and only option...see on details os the filter in Microsoft page docs.microsoft.com/en-us/power-bi/desktop-slicer-filter-date-range
@Rafael you are right. That is a big limitation.
You are the best, hi from Panama!
Hi, like your videos.
Could you do a tutorial on how to combine different graphs and only select each at a time by a dropdown?
Good video
That was nice, Thanks. I would like to know if it is possible to set the default selection on a slicer to the most recent month?, and let the users the option to select another month If they want to.
Nice. So if i wanted last 7 days would i add -7 after (now)?
cool video. What about remaining dates? I may have more dates in my slicer right how do we get reaming dates
I might sound a bit picky :), but the comment was "dynamic selection of the _latest_ date in a slicer", which necessarily hasn't have to be today. Eg.. you make a quality control once a week (with multiple lines of data) and you want the latest quality control only. Relative date on last week doesn't work, since sometimes you perhaps make one week two controls or perhaps you forget one week.You can get the value of the "latest date" with a measure but you cannot use measures in page/report filtering. Any suggestions?
You are so cool! You make Dax fun :)
We try! Thanks for watching 👊
you are awesome man liked your explanation.thank you
Hi Patrick(@Guy in a Cube) Thanks for all amazing tutorials. How do we show last networking working day excluding weekend and holiday?I have two table, calendar table and sales table, i want to show the sales of last working dates. Thanks
Awesome! how to do this for different timezones?
That is so cool! Thanks for sharing.
Thanks for watching Trey!
Hi Patrik,
who can we compare two dates in the same column and find out what's new data is added or removed in the current date?
Thank you for a great tutorial.
I've got some problems still, when i follow your instructions they work great but unfortunatelly i can't choose any other value from my slicer.
Is this normal behavior or am i doing something wrong?
good question
You cant see any other value in the slicer because you have applied report level filter and it is being applied to each data attribute for it, in that case you need to remove report level filter
Thank you, Patrick! It's great info.
Nice one!
Thank you
A good practice when using TODAY() or NOW() in your DAX: don't! You will likely want to test for different conditions based on what "today" is (1st of the month? A weekend day? etc.) So create a measure for TODAY() and reference it so you can later change what it evaluates to, tricking your code into thinking it's a different day. Ex: MyToday = TODAY(), which can later be changed to MyToday = '2020-02-29' to see what happens on a leap year.
Hi,
I want to load data in a table based on Max date for the first time when the report will load, but if user choose another date from date slicer it will load data accordingly. So if user deselect all the dates from the slicer it will load again data based on Max date available in the table.
Can I achieve that?
Hi Patrick, I have one question. How can I filter 2 values in one column using DAX? Thank you so much.
hi, can you use power BI to
create meeting room booking schedule in time period
Great explanation 👍 Thank you
I have data up to NOV 2021 when i am WORKING in 2/Feb/2022. I have created calendar using Min( Fact_Table[Billing-Date]), MAX(Fact_Table[Fisca-lDate]). Now in my slicer i want to show latest Month NOVEMBER dynamically (by NOT selecting manually) & Previous Months. If I get any data like in Dec2021 in the back end, in the slicer i want to see Dec Month whenever i refresh the file. We are using USA fiscal policy. Use DAX ONLY
It is really useful but there will be only one problem while using conditonal formatting thorugh a measure. It doesn't work on dynamic date slicer
Could you please help me in writing a DAX formula or a process which will return a value based on the current day of the week and the corresponding week number?
Assuming today is Week 1, Friday, I want to add a "Card" visualisation to show "Project01" for employee Name01 (assuming I have the separate "Slicer" filter for the "Employee Name").
Then the next time I open my Power BI, which will be on Monday next week, the same "Card" will give me the Monday value of Week 2; which for instance for "Name01" who will be working to a new project called "Project02".
Kinda similar with the one you presented in your video where the data updates based on the current day.
I hope my questions make sense. Thank you in advance for the help.
gr8 job!
I NEED HELP! PLZZ
im trying to do something similar but more specific i guess.
im a server and ive been working on an excel file to track my tips and sales percentages tip outs etc, it's very complex and im completely new to excel so it took me like two months.
i'm trying to recreate an app that i was using but doesn't track enough details.
in this app there are tabs on top for sorting the data yearly, monthly, weekly and biweekly and "all"
and then at the bottom you can toggle between all of the data with arrows, between the 2 arrows there are 2 dates based off of the filter (the corresponding dates of the month, the week etc) but youre not limited to just "this month" and "last month", but you can continue to toggle between all months by clicking the arrow.
i've tried with slicers and it kind of works but not as efficient or specific as i'd like it to be
PLEASE TELL ME if you think that is at all possible on excel
Hi, just joined your channel. :-). Liked your video.
Have you created an if statement in power query (not power BI) where a date in an existing column is greater than or equal to “today” ?
This would allow me to filter only the records with a date greater than or equal to today.
Thoughts ?
Hello! Great tutorial, could you do the same with the actual week or previous week??
Thanks!
Hey there! I'm looking for the same optio. Did you find a solution for the weekly filter?
nice tip
thank you
How to I structure my dataset to show the week to week progress? Week 1, 3 things done. Week 2, 7 things done. Week 3, 15 things done. I want to show between weeks 1 and 2, that 4 things got done, etc..
Hi Patrick!
How can this be tackled in case we are accessing the reports internationally?
Since you are using the NOW() function, it will always take the current system’s date. So once the report goes to the service/publish to web, the NOW() function will use the date according to the location.
So if the report is updated in one country and is being accessed from another, it might not show the last date correctly.
Thanks!
Спасибо! видео из 2018 пригодилось в 2021 =)
Partrick thanks for the streams. I have a support to ask. I have a data with parts effective out dates I want to know as of date what is the no of years has that part crossed. If i use today function its gona reflect every day. Can you help me out. Thanx
Thank you Patrick. Since Now() gives the service/server date/time, it will work for a country but not for other countries. For instance, in France when it's 01:00 AM, it's 07:00 PM in NYC (one day earlier). Can this solution be adapted to calculate the Now() with offset based on the time zone of the viewer ?
Hi! I was wondering, is there any way to show always today's date in a Dates Slicer?
Thanks!
Great video, by the way :)
In theory yes: create a date dimension using a view. Create a column containing the week name, and in case it is the current week label it "current week", in all other cases label it with the real week (e.g. CW17,...). If you set the visual filter for the slicer to "current week" on that column and save the report, it will show always the right week when opening and refreshing it.
Hello,
I use power bi with tabular server and I just use calculated fields. (I cant work with date fields).
I created a GP report . Orders usually record on time . but sometimes orders include turnover but they dont cost, so GP report dont reflect the true GP. It controls once a week
so I would like to report it 7 days before bookings. I use relative date but I dont do that, Is there any solution for this. could you suggest a way to do it
Hello Patrick, I need to filter in a specific way and I could not find anything to help me so far, I need to put two graphs on the same page, which show the current year separated by months and last year separated by months also to be able to compare between one year and the other, but I could not filter it that way, could you help me or point me to a link that might?
Great!
How to show consecutive numbers in y axis dynamically based on slicer selection like 80,81,82--100 please create this video
please help i need to make an autoincrementing filter in power bi on a week basis
Hi Patrick, is there any way we can create page level or report level filter from parameters
Thanks you soo much..
Hey Patrick,
Is it possible to Schedule sending a report with predefined parameters to an email? i'm aware of sending a scheduled report which is simply a snapshot of a report or a Dashboard, but what if i want some filters to be pre-populated before sending that report to a specific email?
Hi, I was wondering how you were able to create the Slicer that says " Select a Date"?
It would be great if the relative date filter had the option for today, this month, Plus and Minus ie so you can set it to show a rolling period centtred on the current date. i.e. Today +/- 1 month, find I need to manually work around this so many times, or ois there a simple workaround I have not found. a lot of the time im working in measures so the column date doesn't always work.
I can see having a slicer default to the current date while still allowing the user to select other dates, but I'm not sure why
anyone would want to make a slicer show only the current date. Why even display the slicer on the page if it's filtered
down to only one choice?
Hello Patrick, how can i get sales projection in power bi.
how would you look at current date + or - 5 days each way?
Is there a way to automatically set date sync slicers to default to the last 3 months but still allowing the end user to change the date on the sync slicer?
ruclips.net/video/5RB3Jdfrn1s/видео.html
Esayas Bemnet thank you for the help suggestion. I will go a little deeper into my issue. I have several reports/visuals across multiple tabs. The first tab is a "filter selections" page, it has several sync slicers with a reset button I created that works off bookmarks. (Resets all selections on the sync slicers back to unselected). On the date sync slicer I have switched from between to relative month calendar.....great this works the way I want it to. But, I cannot use the reset button as it switches the date picker from relative to between / my defaulted relative calendar months to zero. There is an option to ignore data and visuals in bookmarks but this affects then the whole filters page which breaks the sync slicers. Ive also tried using the page filter with a relative date and leaving the date sync slicer as a between. If I hit my reset button it also wipes the page filter settings. It feels like im close to many workarounds/options but then hit a brickwall again....
How could I modify this so it shows Last week, This Week and then all dates.
I am using multiple sources on my dashboard to create different charts, I need to use one date which can be used for all sources and all charts. how am I supposed to do that?
Hi Patrick
Is there any formula for Half To date ? For one semester cumulative dynamic total
Thanks
Hi Patrick.i need help with TotalYTD function.This function returns value till today but also spreads this value for all future dates of the current year.e.g today is 28 Feb 2020,it will calculate YTD e.g 150 till today but also shows 150 for all future dates of year in the visual.is it a way to limit the YTD only till current date
Is it possible to show today as default, and give user option to pick other dates?
Unfortunately no. Have you tried the relative date slicer?