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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.