DAX Fridays!

[@gmalan]

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]

Wonderful!! Thanks for sharing! /Ruth

[@jazzista1967]

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

[@CurbalEN]

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

[@patriciaestherhernandezahj5624]

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

[@hadleyharrison]

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.

[@ultraseb1956]

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

[@CurbalEN]

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

[@joeypuvel1228]

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?

[@EricaDyson]

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

[@CurbalEN]

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

[@9909alex9909]

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.

[@bcippitelli]

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

[@CurbalEN]

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

[@anjelarieder9924]

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!

[@srs1962]

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

[@CurbalEN]

Music to my ears! /Ruth

[@irynatishchenko4192]

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]

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

[@irynatishchenko4192]

@@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]

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

[@vida1719]

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]

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

[@patricefayard2626]

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

[@CurbalEN]

Thank you, you too ;)

[@chamilam]

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]

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

[@chamilam]

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

[@narendrareddy8420]

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)

[@davidcadman3562]

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]

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

[@asepyudhistira6166]

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

[@dirkvanaerden3313]

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]

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

[@jimmyni1983]

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

[@CurbalEN]

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

[@Igor-oc4om]

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

[@CurbalEN]

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

[@Igor-oc4om]

@@CurbalEN thank you very much :D

[@pubudu313]

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) =======

[@mdhidayat5706]

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

[@CurbalEN]

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

[@TheVamos777]

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]

For sure! /Ruth