based on selection cumulative total and previous year value show

[Anonymous]

Hi all,

I want to show previous year value and cummulative total in table fomat

i have source data year, month , i have concatenate year and month (Ex, September 2020),  if i select september 2020,  i need jan to sep2020 cummulative total value,  and previous year 2019 full year value 

sample data

Year	Month	Value	Business
2019	jan	73,431.49	Allocated
2019	april	21,088.96	Consumer
2019	sep	11,203.00	Consumer
2019	dec	58.07	Expenses
2020	jan	13,234.34	Expenses
2020	april	75,690.32	Allocated
2020	sep	17,817.28	Consumer
2020	Oct	46.46	Expenses
2018	jan	71,822.32	Allocated
2018	april	18,293.38	Consumer
2018	sep	69,064.75	Expenses

if i select Sep2020,  i need cummulative total of jan2020 to sep2020 as a CY value ,  2019 jan to Dec value as a PY value

if i select Dec2019, i need cummulative total of jan to Dec2019 as a CY value then 2018 jan to dec as a PY value

output

how can I achieve this logic?

@v-yingjl 

Thanks 

Rajesh

[amitchandak]

@Anonymous , Try Time intelligence with date table

YTD Sales = CALCULATE(SUM(Table[Value]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Table[Value]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[lbendlin]

Strangely enough SAMEPERIODLASTYEAR() will give you _exactly_ what you need. Normally that is a nuisance when you want to compare year over year to date, but in your case that seems to be your desired outcome (full previous year).