Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 an...
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    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.

     

    This is my measure.

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

    Get the expected result.

    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , 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-Last-Week/ba-p/1051123
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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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