Calendar table for YTD and LYTD calculations

[snoya]

Hi,

I am trying to implement YTD and LYTD. I have fact table which has a date column and a calendar table related.

I used YTD calculation as like below:

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

 When I use TOTALYTD formula its calculating entire year, because I have in the calendar table all days until the last day of the actual year. I expected ytd shows data until today.

How can I trim the calendar though dax to show data until a given date or the last day that exists data in the fact table?

Thank you. 

[Ashish_Mathur]

Hi,

Can you show a screenshot of the problem that you are facing.

[snoya]

Hi,

Thank you for your response. I expect YTD for the prior year and actual calculate up to July which is the last month where exists data.

 

[Ashish_Mathur]

Hi,

If your actual data is only till July 2017, then why is there a value of 10 under the Amount column all the way uptil December 2017.  Please share a link from where i can download your workbook.

[snoya]

That is the issue. I dont know why TOTALYTD has that behaviour. The fact table has data until July 2017 but calenadr table has data until December.

Here is the link.

https://drive.google.com/open?id=0Bx6w6EXk0yFhSmx2eWZNTHVyWmM

[Ashish_Mathur · Accepted solution]

Hi,

I removed the Amount column from your visual and created a measure called Amount1

=SUM(FT[AMOUNT])

I then modified your YTD measure to:

=if(ISBLANK([Amount1]),BLANK(),TOTALYTD([Amount1],DIM_DATE[DATE]))

[snoya]

It Works! For the LYTD measure the formula is

if(ISBLANK([Total Amount]),BLANK(),TOTALYTD(Amount1,SAMEPERIODLASTYEAR(DIM_DATE[DATE])))

Thank you for your time. 

[Ashish_Mathur]

You are welcome.