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)
@CurbalEN5 жыл бұрын
Wonderful!! Thanks for sharing! /Ruth
@jazzista19675 жыл бұрын
What a great offset formula using the M code. Thanks.
@CurbalEN5 жыл бұрын
Let me pin the comment for others to see :) /Ruth
@patriciaestherhernandezahj56243 жыл бұрын
Wow, I finally solved my problem. Thank you so much! :)
@hadleyharrison4 жыл бұрын
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!
@janslanina2209 Жыл бұрын
Worked like a charm ! Thank you very much.
@ultraseb19565 жыл бұрын
Gracias Ruth , justo estaba necesitando esto en un reporte y no sabia como hacerlo. Excelente.
@CurbalEN5 жыл бұрын
Perfecto!! Oi el grito de ayuda ;) /Ruth
@joeypuvel12284 жыл бұрын
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?
@EricaDyson5 жыл бұрын
Thanks a lot Ruth. Your solution is so neat and easy to understand :-). Will definitely use it! Great!
@CurbalEN5 жыл бұрын
Simple solutions are always the best, thanks Erica! /Ruth
@9909alex99094 жыл бұрын
Thanks Ruth! Can this logic be added to quarter instead of week?
@joangarcia6327 Жыл бұрын
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.
@bcippitelli5 жыл бұрын
Really nice solution! I did not know it is posible to use WEEK on DATEDIFF! Amazing!
@CurbalEN5 жыл бұрын
I was as happy as you when I saw that ;) /Ruth
@anjelarieder99249 ай бұрын
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!
@srs19624 жыл бұрын
Excellent - banging my head on this and now got it.
@CurbalEN4 жыл бұрын
Music to my ears! /Ruth
@irynatishchenko41925 жыл бұрын
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!!
@CurbalEN5 жыл бұрын
Yes, just change it in the calendar table, I think 0 is sundays and 1 mondays. /Ruth
@irynatishchenko41925 жыл бұрын
@@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.
@CurbalEN5 жыл бұрын
@@irynatishchenko4192 Hou need to do it in Power Query. Change this function: docs.microsoft.com/en-us/powerquery-m/date-dayofweek /Ruth
@vida17195 жыл бұрын
Nice solution. Good to know that a week interval can be used in Dateiff function. I wish a week was available in Dateadd function.
@CurbalEN5 жыл бұрын
Yes, me too! There should be more week functions all together, but thankfully this one was there! /Ruth
@patricefayard26264 жыл бұрын
It’s a great tips, you’re really fantastic ! Thanks you
@CurbalEN4 жыл бұрын
Thank you, you too ;)
@chamilam5 жыл бұрын
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
@CurbalEN5 жыл бұрын
Create the calculated column in your tabular model and import that in power bi :) /Ruth
@chamilam5 жыл бұрын
Curbal thanks, was searching for a solution from Power BI Desktop since we have a lengthy process to follow for backend changes😊.
@narendrareddy84203 жыл бұрын
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)
@davidcadman35625 жыл бұрын
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).
@CurbalEN5 жыл бұрын
Hi David, You should be able to modify this to get you want (if I understood your requirement correctly) m.kzbin.info/www/bejne/Y6mliqeFi96AitU /Ruth
@asepyudhistira61663 жыл бұрын
This is helpful, but what if my week starts on Saturday?
@dirkvanaerden33135 жыл бұрын
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.
@CurbalEN5 жыл бұрын
Probably you need to adjust the calendar to your dates. My guess... /Ruth
@jimmyni19835 жыл бұрын
@Ruth 😍😍😍😍😍😍 🤗🤗🤗🤗🤗🤗 Emojis tell morre than words Hahahaha! Amazing amazing! The simpler, the better! Alwayssss!
@CurbalEN5 жыл бұрын
We are on the same wave: I too love emojis and simple solutions ;) /Ruth
@Igor-oc4om5 жыл бұрын
greeting. you can paste a video link explaining how to make a table date. thanks
@CurbalEN5 жыл бұрын
Hi Igor, Here you have it; curbal.com/blog/create-power-bi-custom-calendars /Ruth
@Igor-oc4om5 жыл бұрын
@@CurbalEN thank you very much :D
@pubudu3134 жыл бұрын
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) =======
@mdhidayat57065 жыл бұрын
do note that PowerBi use Sunday to Saturday to define Week.
@CurbalEN5 жыл бұрын
You can Change that if you have a custom calendar. /Ruth
@TheVamos7775 жыл бұрын
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