CUSTOM PERIOD SLICER IN POWER BI | CUSTOM DATE SLICER IN POWER BI | DATE RANGE OF YOUR OWN CHOICE

Подписаться 11 тыс.

Просмотров 17 тыс.

50% 1

In This Video -
- How to create a custom Date slicer
- How do you make a slicer invisible?
- How do I create a dynamic date slicer in Power BI?
Get Help into Questions Related to #powerbi , #dax , #powerquery Power BI #datamodeling , #analysisservices and #powerbiservice
Checkout our playlist and SUBSCRIBE to our channel, for all such interesting topics in Power BI.
Check out our website www.powerbihelpline.com and blog-post. for end-to-end power BI solutions, Power BI templates, or any kind of Power BI questions like Power query errors, Authoring and optimizing DAX codes for complex business logic.
Follow us on LinkedIn
/ power-bi-helpline-22a2...
Email- info@pbihelpline.com
@POWERBIHELPLINE

Наука

Опубликовано:

14 ноя 2022

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 64

@mnowako Год назад

Excellent! Thank you!

@mirrrvelll5164 Год назад

Great trick!!

@leoheoleoheo5887 Год назад

The part adding INT to the formula is very good, thank you.

@PowerBIHelpline Год назад

Glad it was helpful!

@edwinarchico439 Год назад

as always, excellent and useful.👏

@PowerBIHelpline Год назад

Glad it was helpful!

@CleitondaSilvaLeal Год назад

awesome!!!

@andreicostin3 Год назад

Amazing actually, going to give it a try on my own reports 😮

@PowerBIHelpline Год назад

Glad to know, this video is helpful to you.

@lifewithsunnythedog2107 5 месяцев назад

Thanks for the trick, I have tried this solution, enabling the date field when I select "All" works great, but after i adjust the date and selecting other options like 15days or something date field is not clearing, it keeps showing with the selected date, even the slicer is disabled. Please advice.

@PowerBIHelpline 5 месяцев назад

Thanks for watching. This is one of the known issues with Power BI slicer. where even there are no relevant data for that already selected items, it doesn't automatically clears the selection. So in this case, you need to enable clear selection feature from the header of the slicer, and clear the selection before you select back other slicer options.

@RowellLagman-ht1qg Год назад

Not sure what I was missing. Initially the date slicer is empty when disabled. Selecting date period cause no problem in this state. When previously use "All" the values of FROM and TO, the dates retain even when dates period is selected. in this state, the date slicer is still active even a date period is already selected. The resulting filter is now both in the date period and the date slicer. Please advise.

@PowerBIHelpline Год назад

Thanks for posting your query. It may be due to some existing relationships in your data model, which are not expected to be there for this entire setup. if possible you can send a sample PBIX file to info@pbihelpline.com to check further

@mrrafael161 Год назад

Hi, the same here. If the Date slicer has been used and has been left any different from a very start date and a very last date (narowwer time frame than original) it will affect all selections like Year to Date, Last 15 days, etc.

@user-fy7bv1pb9n 9 месяцев назад

I am having the same issue described above. Any updates on it?@@PowerBIHelpline

@nathanjohnston7166 7 месяцев назад

What was the cause/ solution for this scenario?@@PowerBIHelpline

@oleksiilomaka8707 Год назад

It is a great technique, but it will not work if a visual does not consist dates timeline. For example cards or piecharts. Any idea how to improve it to work for all visuals? Will be very appreciated

@PowerBIHelpline Год назад

Any date dependent slicer will need some date reference to get the last 7 days, last 30 days or any other. however for cards you can set the default dates as current date to get reference to calculate the last 7 days,30 days or any other

@chidichukwumezie8438 11 месяцев назад

Great video. Please what of if you want to do rolling last 1 year from today, how do you go about it?

@PowerBIHelpline 11 месяцев назад

Thanks for watching. Yes you can add that as well same way as we did for last 15 days or last 60 days.

@user-qu5oh6gs6u 9 месяцев назад

My MTD doesn't display the line? its because it returns last month of the year and now I am in October. Could you please suggest a solution how to display the line MTD from the present date?

@richardh99999 6 месяцев назад

This seems to work great with my chart but not with a matrix. Is there any way to adapt it to work with a matrix?

@PowerBIHelpline 6 месяцев назад

You can use the column used in the slicer as the column header of your Matrix visual, and with the relevant row header it should show the relevant values.

@whitenoiseaddict 11 месяцев назад

I just want to show the last 24 months of my measures on a line graph, and it must be changed with a year slicer, for example i choose 2022, december 2022 and january 2021 must be shown, when the slicer value is 2023 because of we are on september, september 2023 and october 2021 must be shown with all the months between them, is there any way to do it ? I can not do that with the way in video.

@PowerBIHelpline 10 месяцев назад

The simplest way to show past 2 years on your month year chart is - 1- Select you visual 2- Drag Date column from the calendar table into visual level filter 3- Select Filter type "Relative" 4- Select Past 2 years This will give you past 2 years data in your visual based on the maximum date available in your calendar table

@whitenoiseaddict 10 месяцев назад

@@PowerBIHelpline I have tried it, it shows last 24 months yes, but from last month of my dataset and never changes with year slicer. I think i am gonna give up. Thank u for help.

@PowerBIHelpline 10 месяцев назад

Please send the sample PBIX file with Dummy data to info@pbihelpline.com

@adrielalmeida4968 9 месяцев назад

uhhhhhhhhhhhhuuuuuuuuuuu You the Bestttttttttttt in the world 🤩🤩🤩🤩🤩🤩🤩🤩

@PowerBIHelpline 9 месяцев назад

Glad it is helpful to you. Thanks for watching

@FredMartino Год назад

Great video could you do the functions for last month, last quarter and last year.

@PowerBIHelpline Год назад

Thanks for the feedback, Yes you can add all above in the same slicer, you just need to define the date ranges for those same way.

@FredMartino Год назад

@@PowerBIHelpline sure but what are the dax functions for last month etc.

@FredMartino Год назад

@@PowerBIHelpline sure but what are the dax functions for last month etc.

@user-sx7rt2yv5h 3 месяца назад

can you please provide the formula for custom dates dax?

@PowerBIHelpline 3 месяца назад

To get the PBIX file used in the demo, you can send an email by mentioning video title as email subject

@15leminhhieu46 11 месяцев назад

My data consists of 12 months of 2023, but the actual data only reaches May 2023. So should I alter your DAX to have a correct MTD or QTD period slicer with my actual data to May 2023? Thank you

@PowerBIHelpline 11 месяцев назад

for MTD and QTD, we have used time intelligence function, which automatically checks the maximum date in the context, and calculates the MTD for the month it belong to or its corresponding QTD. but if you have any custom requirement, obviously you can modify the DAX accordingly.

@user-qu5oh6gs6u 9 месяцев назад

how ?@@PowerBIHelpline

@user-ro4kw7on4j 10 месяцев назад

how can i add this month and this year

@PowerBIHelpline 10 месяцев назад

You can add same way by extending the condition with validation on the month with MONTH(TODAY) or YEAR(TODAY), however such options you can also see directly, if you use the date slicer of type "Relative" (from the filter section)

@sushreesangeetajena7646 Год назад

I am getting an error stating, you can't compare date with text. I am not able to load visual. Please help

@sushreesangeetajena7646 Год назад

And I can't use Today() because my dataset has historical dates between 2013 and 2015. Please advise how to resolve this Thank you

@PowerBIHelpline Год назад

Please validate your solutions with the Demo video, if everything is same, and you haven't missed anything, it should work same way as it is working in this demonstration.

@mrrafael161 Год назад

Hi, in 99% of cases when date with thext format is being compared, the solution is very simple - go to your date table and make sure dates are formated as dates, yeaars as whole number, months as whole number, etc. It should do the trick.

@mrrafael161 Год назад

@@sushreesangeetajena7646 , Hi, if your dataset has historcal dates ending long before today's current date, the solution is very simple - create a variable(!) using LASTDATE and as the argument use your Dates[date] table and column. Instead of using all the logic with TODAY(), use your variable (lastdate). In this particular case, I use LASTDATE of my disconneced Date table.

@tejaswilingamsetty7031 2 месяца назад

Actually I need to create a slicer to get this values Based on today's date. current week and next week has to be considered as Red Next 2 to 4 weeks as Amber Greater than 4 weeks as Green If blank then yellow Can you please guide me how to achive this.

@PowerBIHelpline 2 месяца назад

Not sure what exactly you are looking for. do you want to see these colors inside your slicer?? else it looks pretty straight forward.

@tejaswilingamsetty7031 2 месяца назад

Yes. I need to place them in a slicer. I have created a calculated column to achieve this using a switch condition based on the current week and current year. The issue here is, for example, if we are in December 30, 2024, then the week continues till January 4, 2025. How can I handle the transitions between weeks if the year changes?

@workstuff5253 Год назад

This computer generated voice reminds me of "Ron's Gone Wrong" 🤣

@PowerBIHelpline Год назад

All we are looking for to bring the solutions for challenging Power BI problem.

@Bunker27-Apparel 5 месяцев назад

I am getting a syntax error in this part of the video. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Su1mERlwNdM.htmlsi=Yg8hFlOvuafr11Jp&t=152. I've gone through it 5 times to make sure I am not missing anything, and still not working.

@PowerBIHelpline 5 месяцев назад

Since this is a syntax error, we need to look into the DAX expression to suggest what could be the possible reason for this.

@diptivishwakarma3752 Год назад

The number of arguments is invalid. Function CONTAINSROW must have a value for each column in the table expression. Showing error in this dax

@PowerBIHelpline Год назад

Where are you using above DAX function? please give more context about your query.

@divyadasari2004 Год назад

Getting same error

@tiffany1115 Год назад

Getting the same error. Period Filter = VAR _selectedperiod = SELECTEDVALUE('Last 5 Custom Period'[Period-Order]) VAR _last5qtr = MAX('Date'[Date]) IN FILTER('New Date', 'New Date'[Fiscal Year Quarter Offset] >= -4 && 'New Date'[Fiscal Year Quarter Offset] = -4 && 'New Date'[Fiscal Year Month Offset] = -4 && 'New Date'[Fiscal Year Week Offset]

@tiffany1115 Год назад

I tried the "_15days" calculation within my PBI report and also getting the error in DAX. Know how to remediate this error?

@PowerBIHelpline Год назад

@@tiffany1115 You might be getting errors because of a different setup in your data model. Please send an email to info@pbihelpline.com to get the PBIX file used in above demo.

@richardh99999 6 месяцев назад

Help please re Error: MdxScript(Model) (24,26) Calculation error in measure 'Custom Period'[Period Filter]: Function 'SWITCH' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values. Here is my measure: Period Filter = VAR _selectedperiod = SELECTEDVALUE('Custom Period'[Order]) VAR _mtd = MAX(dim_Date[Date]) IN DATESMTD('New Date'[Date]) VAR _qtd = MAX(dim_Date[Date]) IN DATESQTD('New Date'[Date]) VAR _10days = MAX(dim_Date[Date]) IN FILTER('New Date', 'New Date'[Date] = TODAY() -10) VAR _30days = MAX(dim_Date[Date]) IN FILTER('New Date', 'New Date'[Date] = TODAY() -30) VAR _60days = MAX(dim_Date[Date]) IN FILTER('New Date', 'New Date'[Date] = TODAY() -60) VAR _all = MAX(dim_Date[Date]) IN ALL('New Date'[Date]) RETURN INT(SWITCH( _selectedperiod, 1, _mtd, _selectedperiod, 2, _qtd, _selectedperiod, 3, _10days, _selectedperiod, 4, _30days, _selectedperiod, 5, _60days, _selectedperiod, 6, _all ))

@richardh99999 6 месяцев назад

I think I've solved it by this change in the SWITCH structure Period Filter = VAR _selectedperiod = SELECTEDVALUE('Custom Period'[Order]) VAR _mtd = MAX(dim_Date[Date]) IN DATESMTD('New Date'[Date]) VAR _qtd = MAX(dim_Date[Date]) IN DATESQTD('New Date'[Date]) VAR _10days = MAX(dim_Date[Date]) IN FILTER('New Date', 'New Date'[Date] = TODAY() -10) VAR _30days = MAX(dim_Date[Date]) IN FILTER('New Date', 'New Date'[Date] = TODAY() -30) VAR _60days = MAX(dim_Date[Date]) IN FILTER('New Date', 'New Date'[Date] = TODAY() -60) VAR _all = MAX(dim_Date[Date]) IN ALL('New Date'[Date]) RETURN INT(SWITCH( _selectedperiod, 1, _mtd, 2, _qtd, 3, _10days, 4, _30days, 5, _60days, 6, _all ))

@blandinomucumbe1451 Год назад

my code does not work error = Syntax for var is incorrect dax Period Filter = VAR _selectperiod = SELECTEDVALUE('Custom Period'[Order]) VAR _mtd = MAX(Dim_Date[Date] IN DATESMTD('New Date'[Date]) VAR _qtd = MAX(Dim_Date[Date] IN DATESQTD('New Date'[Date]) VAR _lastday = MAX(Dim_Date[Date]) IN FILTER('New Date', 'New Date'[Date] = TODAY()-1) VAR _7days = MAX(Dim_Date[Date]) IN FILTER('New Date', 'New Date'[Date] = TODAY()-7) VAR _15days = MAX(Dim_Date[Date]) IN FILTER('New Date', 'New Date'[Date] = TODAY()-15) VAR _30days = MAX(Dim_Date[Date]) IN FILTER('New Date', 'New Date'[Date] = TODAY()-30) VAR _all = MAX(Dim_Date[Date]) IN ALL('New Date'[Date]) RETURN INT(SWITCH( _selectperiod, 1, _mtd 2, _qtd 3, _30days 4, _15days 5, _7days 6, _lastday 7, _all ))

@PowerBIHelpline Год назад

in 2 variqbles _mtd and_qtd, you forgot to close the parenthesis of MAX()

@shantanupatil4538 2 месяца назад

VAR_ selectedperiod = SELECTEDVALUE( 'Custom period'[order]) VAR _mtd = MAX(dim _Date[Date]) IN DATESMTD( 'New Date' [Date]) VAR _qtd = MAX(dim_Date[Date]) IN DATESQTD( 'New Date'[Date]) VAR_ 15days = MAX(dim_Date[Date]) IN FILTER ( 'New Date', 'New Date' [Date] = TODAY() - 15) VAR _60days = MAX(dim_Date[Date]) IN FILTER ( 'New Date', 'New Date'[Date]