Forum Discussion
based on selection cumulative total and previous year value show
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?
Thanks
Rajesh
2 Replies
- lbendlin
Super User
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).
- 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.
