DAX Fridays!
ฝัง
- เผยแพร่เมื่อ 5 ต.ค. 2024
- 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/don...
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/cou...
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/...
▼▼▼▼▼▼▼▼▼▼
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/pro...
Many thanks in advance!Here you can download all the pbix files: curbal.com/don...
▲▲▲▲▲▲▲▲▲▲
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
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)
Wonderful!! Thanks for sharing!
/Ruth
What a great offset formula using the M code. Thanks.
Let me pin the comment for others to see :)
/Ruth
Wow, I finally solved my problem. Thank you so much! :)
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!
Gracias Ruth , justo estaba necesitando esto en un reporte y no sabia como hacerlo. Excelente.
Perfecto!! Oi el grito de ayuda ;)
/Ruth
Worked like a charm ! Thank you very much.
Thanks a lot Ruth. Your solution is so neat and easy to understand :-). Will definitely use it! Great!
Simple solutions are always the best, thanks Erica!
/Ruth
Really nice solution! I did not know it is posible to use WEEK on DATEDIFF! Amazing!
I was as happy as you when I saw that ;)
/Ruth
Excellent - banging my head on this and now got it.
Music to my ears!
/Ruth
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?
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!
Nice solution. Good to know that a week interval can be used in Dateiff function. I wish a week was available in Dateadd function.
Yes, me too! There should be more week functions all together, but thankfully this one was there!
/Ruth
It’s a great tips, you’re really fantastic ! Thanks you
Thank you, you too ;)
This is helpful, but what if my week starts on Saturday?
Thanks Ruth! Can this logic be added to quarter instead of week?
@Ruth 😍😍😍😍😍😍 🤗🤗🤗🤗🤗🤗 Emojis tell morre than words Hahahaha! Amazing amazing! The simpler, the better! Alwayssss!
We are on the same wave: I too love emojis and simple solutions ;)
/Ruth
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.
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)
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!!
Yes, just change it in the calendar table, I think 0 is sundays and 1 mondays.
/Ruth
@@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.
@@irynatishchenko4192 Hou need to do it in Power Query. Change this function:
docs.microsoft.com/en-us/powerquery-m/date-dayofweek
/Ruth
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).
Hi David,
You should be able to modify this to get you want (if I understood your requirement correctly)
m.th-cam.com/video/-xBYtOVyMTs/w-d-xo.html
/Ruth
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.
Probably you need to adjust the calendar to your dates. My guess...
/Ruth
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
Create the calculated column in your tabular model and import that in power bi :)
/Ruth
Curbal thanks, was searching for a solution from Power BI Desktop since we have a lengthy process to follow for backend changes😊.
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)
=======
greeting. you can paste a video link explaining how to make a table date. thanks
Hi Igor,
Here you have it;
curbal.com/blog/create-power-bi-custom-calendars
/Ruth
@@CurbalEN thank you very much :D
do note that PowerBi use Sunday to Saturday to define Week.
You can Change that if you have a custom calendar.
/Ruth
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
For sure!
/Ruth