Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mordonez
Frequent Visitor

Personalized Date Table Weeks with Index

Hello , i have to build a report , from data wich is manually asign the week (no date column , and sometimes with 6 weeks) 

That makes impossible use a date table 

I figured to use a index table and relate to the data table creating a index and creating a order column to set the order of the weeks by month and year 

What im having trouble is to get the data in a Week over Week format , 

Example 

im in the week 4 in december and i like to get the data for last week using the index table and order column 

 

i tried to use filter and all , but when i put the measure in the table it shows blank 

Attached is the example PBIX 

But this is a sample of the data table

week_index  MONTH  week  pais  sub  actuals  pc
FY22-Nov-W2  NovW2ECKPFOV1000BIO22
FY22-Nov-W1  NovW1ECKPFOV2000BIO22

and this is the sample of the index table i created 

FY  MONTH  WEEK  INDEX  ORDER
FY22NovW2FY22-Nov-W237
FY22NovW1FY22-Nov-W136

 

What i like to archieve is when i select a week in a filter and a month in actuals -1 i get the value from last week :

 

PC  ACTUALS (selected in filter nov , w2)  ACTUALS -1 (last week) measure???
BIO22  1000  2000
   
1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @mordonez ,

According to your description, you want to get the last week's actual by the week index filter, here's my solution.

This is my sample data.

vkalyjmsft_0-1640844725713.png

vkalyjmsft_1-1640844748168.png

vkalyjmsft_2-1640844796083.png

 

This is my measure.

Measure = 
CALCULATE (
    MAX ( 'Table'[Actuals] ),
    FILTER ( ALL ( 'Index' ), 'Index'[ORDER] = MAX ( 'Index'[ORDER] ) - 1 )
)

Get the expected result.

vkalyjmsft_3-1640844862864.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yanjiang-msft
Community Support
Community Support

Hi @mordonez ,

According to your description, you want to get the last week's actual by the week index filter, here's my solution.

This is my sample data.

vkalyjmsft_0-1640844725713.png

vkalyjmsft_1-1640844748168.png

vkalyjmsft_2-1640844796083.png

 

This is my measure.

Measure = 
CALCULATE (
    MAX ( 'Table'[Actuals] ),
    FILTER ( ALL ( 'Index' ), 'Index'[ORDER] = MAX ( 'Index'[ORDER] ) - 1 )
)

Get the expected result.

vkalyjmsft_3-1640844862864.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

VahidDM
Super User
Super User

Hi @mordonez 

 

Please see this link:

 

https://www.vahiddm.com/post/weekly-time-intelligence-dax

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

amitchandak
Super User
Super User

@mordonez , refer if my WOW blog can help

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

 

 

measure example using week rank in a date table

 

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

 

 

new columns in date table

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

Hello ,thank you for you answer , but is not what i asked , i dont have a date table , because the data has no date field 

The field used to relate the data and the index is the week_index field , that is because that information comes from the folder where the files are hosted 
EX : 
with powerquery , when i get the data i use the folder name ( FY , Month, Week) to order the data in time

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors