Is there a measure to automate current and previous year from date column and do the calculations?

[Anonymous]

Is there a measure to automate current and previous year from date column and do the calculations?

I have past 2 years data and the data will add on every day and I have done all the measures on the year 2017 and 2018. Now the problem raised, when the data for 2019 is added into the sheet is it not fetching it. it is taking 2017 and 2018. I need a measure which will take previous year and current year automatically when new data is added and removes the old data(if possible).

all the calcualtion will automatically change as per the previous and current year.

Thanks in advance.

Regards

Phaneendra

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

Hi Phaneendra,

Can you share a sample, please? Please mask the private data.

The DAX function is dynamic. It will calculate the new data automatically. What is your scenario? If the Power BI doesn't get the new data, you need to refresh the report or dataset.

Best Regards,
Dale

[Anonymous]

date	details	sales
1/01/2017	a	45
1/02/2017	s	646
1/05/2017	d	36
5/06/2017	f	1
9/12/2017	g	4
1/01/2018	h	464
5/12/2018	rtr	3
6/01/2018	cv	13
9/01/2018	df	316
1/01/2019	f	3
2/01/2019	re	1

today we are in 2018 and I have return a measure as 2017 total - 2018 total and in next year if we add 2019 data, the measure where I have return 2017 and 2018 will fail. What I have to do so that the PBI will automtically change 2019 has current year and 2018 has previous year. I simple on 2 jan 2019 if I run the report it should take the measure as 2018 - 2019(insted of 2017 -2018). Hope I am clear.
I need a measure where It will directly take all 2017 has previous and 2018 has current year. and In next year 2018 has previous year and 2019 has current year.

Hope I am clear.

[v-jiascu-msft]

Hi @Anonymous,

Do you have a Date table? If so, the formulas could be like below.

previous =
CALCULATE ( SUM ( 'table'[sales] ), PREVIOUSYEAR ( 'date'[date] ) )

current = sum('table'[sales])

Best Regards,
Dale

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship between the Date column of your base data table to the Date column of the Calendar Table.  In the Calendar Table, create these calculated column formulas: Year=Year(Calendar[Date]) and Month=FORMAT(Calendar[Date],"mmmm").  In your visual, drag Year from the Calendar Table.  Write this measure

=SUM(Data[Sales])

Hope this helps.