Powerpivot/dax help

[svishwanathan]

Hi 

I have a spreadsheet that contains  open ticket information. We have added a column called Reporting Date which captures when the report was downloaded to help us with doing some snapshot trending. Usually it is done once a week but sometimes the interval can be 10 days or more.

I want to be able to index or give a number to these reporting dates

For instance, the last reporting date was 1/21. so that row should contain 0 and the previous reporting period was 1/14...so those rows to contain -1

Report date            Relative Index

1/21/2018                   0

1/21/2018                   0

1/14/2018                    -1

1/21/2018                    0

1/7/2018                       -2

[v-yuezhe-msft · Accepted solution]

@svishwanathan,

A bit change to Phil_Seamark's DAX formula.

Column = 
VAR x = MAX(Table[Report Date]) - (WEEKDAY(MAX(Table[Report Date]),1)-1)
RETURN-DATEDIFF(Table[Report Date],x,WEEK)

Regards,
Lydia

[Phil_Seamark]

Column = 
VAR x = TODAY() - (WEEKDAY(TODAY(),1)-1)
RETURN-DATEDIFF('Table2'[Report Date],x,WEEK)

This calculated column may be what you are after

[Phil_Seamark]

HI @svishwanathan

Are the dates always Sunday?