Forum Discussion

Psyklone's avatar
Psyklone
New Member
9 years ago
Solved

Relative WTD MTD & YTD values

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.

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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