Relative WTD MTD & YTD values

[Psyklone]

Hi,

Our business does some pretty standard Last week, MTD, and YTD reporting of sales etc.

There are plenty of ways to show this information, however how would I do it AND allow the user to pick which week/day this reporting up to.

For instance, lets say weve just finished week 9. So last week will be Week 9, MTD would be weeks 6-9 (give or take), and YTD would be weeks 1-9 (of the chosen year).

I can set up a report to show this data, but once we advance past week 10 how can I allow the user to see week 9's version of the report again? If I use a slicer and they pick week 9, then MTD and YTD won't work as it won't sum all the relevant records, only those with a week equal to 9.

Do I structure my data differently, or can I make a slicer linked to just one set of information?

Would I need to create measures to calculate if a sales record was part of the MTD and YTD data? And how would I reference the slicer selection to calculate this?

I feel like I'm missing something obvious.

[v-shex-msft · Accepted solution]

Hi @Psyklone,

You can refer to below formulas if it suitable for your requirement:

MTD and YTD measure formulas:

MTD = CALCULATE(SUM(Fact[Amount]),DATESMTD('Calendar'[Date])) 

YTD = CALCULATE(SUM('Fact'[Amount]),DATESYTD('Calendar'[Date]))

For week number to date, you can create a new table to store these week numbers, then use it as the source of slicer, and write measures based on it.(notice: please don't build relationship between this table to another tables)

Week number table:

Table = SELECTCOLUMNS('Calendar',"Week",WEEKNUM('Calendar'[Date],1)) 

WTD measure:

WTD = 
var current_date=MAX('Fact'[Date])
var selected = if(HASONEVALUE('Table'[Week]),VALUES('Table'[Week]),BLANK())
return
CALCULATE(SUM('Fact'[Amount]),YEAR('Fact'[Date])=YEAR(current_date),WEEKNUM('Calendar'[Date],1)<selected)

Notice: if you want the week slicer also works on mtd and ytd, you can add the weeknum filter to above formulas.

var selected = if(HASONEVALUE('Table'[Week]),VALUES('Table'[Week]),BLANK())

WEEKNUM('Calendar'[Date],1)<selected)

Regards,

Xiaoxin Sheng