Previous Week Days Calculated Field

DAX DAX Calculations
1

I have a custom week number field in my data which is called “custom week Number” (Friday as 1st day of week and Thursday of Next week is the end date of the week).

Now I want to create a calculated column in power BI which should give me all individual days from the previous week NOT the current week.
I want to use that calculated field on my X-Axis of the visual so that I should always default to the previous week and should show the days of that week on my X Axis as (Friday, Mon,Tues,Wed and Thursday). When the week changes, it should automatically update to the most recent previous week.
How can I achieve this?

Thanks

2

can you give an example

3

Hello VilmarSchi,
Here is the further explanation with example of data from 2024. I tried uploading a picture to this explination (Which shows the sample data) Not sure if you can see that!! If not BELOW is the explanation.

Let me explain step by step.

  1. Say that my week # column for this year shows #1 as first week (Jan-1 to Jan 4) and #2 week (Jan-5th to Jan 11th) and week#3 which is latest ot Current week (Jan 12th and Jan 13th/Today, Other days are yet to complete).
  2. I want to create a calculated field which show Previous week (or Week #2 in this case) always. When the week changes to week #4 the field has to show only week #3 untill the week changes to week#5 and so on.
  3. I have a requirement to create a stacked column chart. On the chart I want to use date field on X-Axis and want to use this new calculated column as filter on visual so that the X-Axis should always defaults to previous week AUTOMATICALLY with out manual change of the week.
    Let me know if you are unclear of anything in the above scenario

IMG_0685
IMG_06851920×1440 148 KB

4

@durgacpb87 - you need to create indicator IsLastWeek (which you calculate automatically) and then filter base on that

for Example you can use

if [custom week Number] = Date.WeekOfYear(DateTime.LocalNow() )-1 then 1 else 0

You can check in Pbi
DatSample.xlsx (11.1 KB)
Sampple.pbix (24.7 KB)

Hope that is what are you looking for

1 Like
5

See if it helps

Custom Week Prev = 
OFFSET( -1, ALL( dCalendario[Custom Week] ) )

image
image960×385 26.5 KB

1 Like
6

Hello @mspanic. It works as I expected. Thanks a lot.

7

Hello @VilmarSchi. Thanks for your input. The DAX you suggested works.

Alternatively, I also tried using the follwing logic.

Previous Week = [Custom week]-1
Then using this as a filter on visual (Selected TOP N “Advanced Filter” option and set to TOP-1) This also works.

Once again thanks a lot for your input