DAX Fridays!

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

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

50% 1

When the custom date filter in Power BI doesn't do what you need, how you create your own? What if you need to do it by week? Lets find out in today's #daxfridays video. #powerbi #dax #curbal #daxfridays
Get Northwind Dataset: • Northwind data source:...
Link to DAX Fridays survey: bit.ly/2MMM4KK
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
curbal.com/product/sponsor-me
Many thanks in advance!Here you can download all the pbix files: curbal.com/donwload-center
▲▲▲▲▲▲▲▲▲▲
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#daxfridays #curbal #dax #powerbi #mvp

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

4 июл 2019

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

@gmalan 5 лет назад

Thanks Ruth. This is how I did it in Power Query (for a week ending on a Sunday) Week offset custom column formula: =Number.RoundDown( (Number.From([Date]) - (Number.From(DateTime.LocalNow()) - Date.DayOfWeek(DateTime.Date(DateTime.LocalNow()),Day.Monday))+1)/7)

@CurbalEN 5 лет назад

Wonderful!! Thanks for sharing! /Ruth

@jazzista1967 5 лет назад

What a great offset formula using the M code. Thanks.

@CurbalEN 5 лет назад

Let me pin the comment for others to see :) /Ruth

@patriciaestherhernandezahj5624 2 года назад

Wow, I finally solved my problem. Thank you so much! :)

@janslanina2209 Год назад

Worked like a charm ! Thank you very much.

@EricaDyson 5 лет назад

Thanks a lot Ruth. Your solution is so neat and easy to understand :-). Will definitely use it! Great!

@CurbalEN 5 лет назад

Simple solutions are always the best, thanks Erica! /Ruth

@hadleyharrison 3 года назад

Thank you so much! I was having trouble filtering a dashboard by week number so it displayed week X of the current year and the previous year, and this was a great jumping off point!

@ultraseb1956 5 лет назад

Gracias Ruth , justo estaba necesitando esto en un reporte y no sabia como hacerlo. Excelente.

@CurbalEN 5 лет назад

Perfecto!! Oi el grito de ayuda ;) /Ruth

@srs1962 4 года назад

Excellent - banging my head on this and now got it.

@CurbalEN 4 года назад

Music to my ears! /Ruth

@bcippitelli 5 лет назад

Really nice solution! I did not know it is posible to use WEEK on DATEDIFF! Amazing!

@CurbalEN 5 лет назад

I was as happy as you when I saw that ;) /Ruth

@patricefayard2626 3 года назад

It’s a great tips, you’re really fantastic ! Thanks you

@CurbalEN 3 года назад

Thank you, you too ;)

@vida1719 5 лет назад

Nice solution. Good to know that a week interval can be used in Dateiff function. I wish a week was available in Dateadd function.

@CurbalEN 5 лет назад

Yes, me too! There should be more week functions all together, but thankfully this one was there! /Ruth

@jimmy3862 5 лет назад

@Ruth 😍😍😍😍😍😍 🤗🤗🤗🤗🤗🤗 Emojis tell morre than words Hahahaha! Amazing amazing! The simpler, the better! Alwayssss!

@CurbalEN 5 лет назад

We are on the same wave: I too love emojis and simple solutions ;) /Ruth

@TheVamos777 5 лет назад

Always like to put offsets for day, week month in my date tables. Once you have them you soon find out how hand they are

@CurbalEN 5 лет назад

For sure! /Ruth

@9909alex9909 4 года назад

Thanks Ruth! Can this logic be added to quarter instead of week?

@joeypuvel1228 3 года назад

Is this possible to do based off of the bottom date value in another date slicer in the dashboard, instead of based off of today's date?

@davidcadman3562 5 лет назад

Great as usual. This got me thinking about the problem of showing Sales Previous Year for the equivalent MTD. Maybe you already have a video on that? I added column in the calendar table called Date[Date Is After Yesterday] and then my measure [Sales PY] gives me the number for July 2018 to 14th only when I filter the new column to "Y". Is this an OK solution or is there something better? Maybe this could be a video topic (unless you already have one).

@CurbalEN 5 лет назад

Hi David, You should be able to modify this to get you want (if I understood your requirement correctly) m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE--xBYtOVyMTs.html /Ruth

@anjelarieder9924 4 месяца назад

Thank you, Ruth!!!! Still very useful in 2024 👍how can I filter SPLY, % YY? on week base f.e. for last 4 weeks in 2024 and LY 2023? Many thanks!

@irynatishchenko4192 4 года назад

That's super helpful!! My only concern is that the weeks start with Sundays, not Mondays..Is there a way to fix that in the example you shared? Thanks!!

@CurbalEN 4 года назад

Yes, just change it in the calendar table, I think 0 is sundays and 1 mondays. /Ruth

@irynatishchenko4192 4 года назад

@@CurbalEN not sure what you mean. Do I need to adjust the formula shown in the video? In my Calendar Column WeekNum starts from Monday (2). But that doesn't help to make the Relative week filter start counting weeks from Monday not Sunday. Thanks in advance.

@CurbalEN 4 года назад

@@irynatishchenko4192 Hou need to do it in Power Query. Change this function: docs.microsoft.com/en-us/powerquery-m/date-dayofweek /Ruth

@joangarcia6327 8 месяцев назад

Hola Ruth, no acabo de encontrar el video que me ayuda. Estoy comparando producciones reales diarias con Budget mensual. Para ello he creado una medida solo para Budget con ALL(CALENDAR[DATE.DIA]. Así, aunque filtre las fechas sin el día 1 del mes, me enseña el budget de todo el mes. Todo ok hasta que he puesto el filtro de fecha con un slide. No funciona, ya que el filtro slide no permite jerarquía de fechas, te lo cambia a "menu vertical" o "lista desplegable" . Sabes como solucionarlo? Me gusta el filtro slide, es más elegante ;D. Gracias por anticipado.

@chamilam 4 года назад

Thanks a lot Ruth, could you please tell me how ca I apply this filter when we have a live connection to the Tabular model. Since no calculated columns are allowed over live connection. I want to filter my date slicer 4 quarters past and 1 quarter to the future Thanks

@CurbalEN 4 года назад

Create the calculated column in your tabular model and import that in power bi :) /Ruth

@chamilam 4 года назад

Curbal thanks, was searching for a solution from Power BI Desktop since we have a lengthy process to follow for backend changes😊.

@asepyudhistira6166 2 года назад

This is helpful, but what if my week starts on Saturday?

@narendrareddy8420 3 года назад

How do we filter data for a relative date which looks something like this (minimum date in column) to (maximum date in column minus 30days)

@dirkvanaerden3313 5 лет назад

Hi Ruth, I'll try to add this to my model but I get the next error : the start date can not be greater as the end date.

@CurbalEN 5 лет назад

Probably you need to adjust the calendar to your dates. My guess... /Ruth

@Igor-oc4om 4 года назад

greeting. you can paste a video link explaining how to make a table date. thanks

@CurbalEN 4 года назад

Hi Igor, Here you have it; curbal.com/blog/create-power-bi-custom-calendars /Ruth

@Igor-oc4om 4 года назад

@@CurbalEN thank you very much :D

@mdhidayat5706 5 лет назад

do note that PowerBi use Sunday to Saturday to define Week.

@CurbalEN 5 лет назад

You can Change that if you have a custom calendar. /Ruth

@pubudu313 4 года назад

Hello Ruth, Hope you stay safe. I was literally stuck in following scenario. I even can think is this fundamentally wrong thinking? I want to SUMARIZE to new table (or create another table) from an existing table where "TransactionDate"of each records should be less than or equal number of days that user select from a WHATIF parameter value. I wrote following DAX. But its not working. Can you please help me? Sorry for asking here.. KDimTimeEntered = ADDCOLUMNS(SUMMARIZE(FILTER(FactTimeLog, FactTimeLog[DateParsed] = (TODAY()-WhatIfSelectedDate)), DimKey[Key], FactTimeLog[Time Spent (hr)]), "Status", "Entered") =======Measure WhatIfSelectedDate = CALCULATE((TODAY() - WhatIfDaysBefore[WhatIfDaysBeforeSelectedValue])) ====== What if parameter WhatIfDaysBefore = GENERATESERIES(0, 31, 1) =======