YTD and YTD prior with filter

[erihsehc]

hi there,

I would like to compare YTD against last year YTD. I created 2 measures as below, however, when I selected the current year in slicer, the last year YTD become blank. How to solve this, thanks

sales = CALCULATE(SUM(Sheet1[amount]),DATESYTD(Sheet1[data]))

LY sales = CALCULATE([sales],SAMEPERIODLASTYEAR(Sheet1[data]))

Data model as below

data model

Best regards,

ER

[Ashish_Mathur · Accepted solution]

Hi @erihsehc,

Problem Solved.  Download the file from here.

[erihsehc]

thanks @Ashish_Mathur.It works but need to add one more table to the file. I am a bit picky that want to use the same data model but using dax to solve it

[Ashish_Mathur]

Hi,

You are welcome.  Whenever there is a date column in your data table, it is a best practise to create a Calendar Table.  This is so that you can use Date/Time Inteligence functions.

That is all i will leave it at.

[erihsehc]

hi @Ashish_Mathur, it is a very good idea

I have another question that, I would like to create a YTD table as below, it requires to select all months. how to get this table created by just selecting a single YTD month (for the below table, I would like to select only July)? thanks

Data model is as below

data model

Best regards,

ER

[Ashish_Mathur]

Hi,

Sorry but i do not know how to do that.  If my previous helped, please mark it as Answer.

[Abduvali]

Hi @erihsehc,

If you want to use slicer and still see the last year sales then you have to use something like this:

• LY sales = CALCULATE(SUM(Sheet1[amount]),FILTER(ALL(Sheet1),Sheet1[year ] = Year(TODAY())-2))
• change -2 to -1 to see 2016 sales

 Enjoy

Regards

Abduvali

[Zubair_Muhammad]

hi @erihsehc

Try this one

LY sales =
CALCULATE (
    [sales],
    ALL ( Sheet1 ),
    SAMEPERIODLASTYEAR ( DATESYTD ( Sheet1[data] ) )
)

[erihsehc]

thanks @Zubair_Muhammad, it works but if I used ALL function, the other filters will not work if I want to add some filters, how to make other filter works as well

[BeemsC]

I don't really understand the question.
You make a measure to only show the data from 2015 (LY)
And then you filter the data on your dashboard to only show 2016, naturally the data dissapears.

If you want to compare the 2, just put them both in your desired visual?

Or am i misunderstanding?

[erihsehc]

hi @BeemsC, basically, user can select 2016 in slicer, but I also want to show the 2015 YTD on the card

[Zubair_Muhammad]

Hi @erihsehc

Try this..........

LY sales =
CALCULATE ( [sales], SAMEPERIODLASTYEAR ( DATESYTD ( Sheet1[data] ) ) )

[erihsehc]

hi @Zubair_Muhammad, I tried but seems not working