Last year YTD

[Anonymous]

Hi,

    I need to calculate the Last year YTD value. Say if today's date is Feb 24th 2020 i need to compare current year YTD value to last year YTD value i.e till 24th Feb 2019. I used a formula as

YTD= CALCULATE([Enquiry],DATESYTD(Date[Date]))

YTD_LY = CALCULATE(YTD,SAMEPERIODLASTYEAR(Date[Date]))

When i use this query i getting last year value till 28th Febuary 2019 but i need to get only till 24th Feb 2019 since my current date is 24th Feb 2020

[v-lili6-msft · Accepted solution]

HI  @Anonymous 

The simple way is that add a IF conditional in the formula

YTD = IF(MAX('Date'[Date])<=TODAY(),CALCULATE([Enquiry],DATESYTD('Date'[Date])))

YTD_LY = IF(MAX('Date'[Date])<=TODAY(),CALCULATE([YTD],SAMEPERIODLASTYEAR('Date'[Date])))

Regards,

Lin

[amitchandak]

Giving Date can make YTD work best.

Other You can try

YTD= CALCULATE([Enquiry],DATESYTD(Date[Date]), Date[Date]<=today())
YTD_LY = CALCULATE(YTD, dateadd(Date[Date],1,Year)<=today())

Or you can put a visual level filter by drag the date from calendar and date in last 2 year including today

[vivran22]

Hello @Anonymous 

I believe, it has to do with the slicers you have applied on the report. 

I have recreated a  sample visual for this:

Below I have used the slicers on Year & Month:

and below I have used date as slicer:

When you are selecting Feb 2012 in the slicer, it is picking all the dates within Feb. 

Cheers!
Vivek


If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂


https://www.vivran.in/

Connect on LinkedIn