Reply
Frequent Visitor
Posts: 4
Registered: ‎12-05-2018

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

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

Community Support Team
Posts: 7,295
Registered: ‎05-02-2017

Re: Is there a measure to automate current and previous year from date column and do the calculation

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 4
Registered: ‎12-05-2018

Re: Is there a measure to automate current and previous year from date column and do the calculation

datedetailssales
1/01/2017a45
1/02/2017s646
1/05/2017d36
5/06/2017f1
9/12/2017g4
1/01/2018h464
5/12/2018rtr3
6/01/2018cv13
9/01/2018df316
1/01/2019f3
2/01/2019re1

 

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.

Super User
Posts: 3,737
Registered: ‎01-14-2017

Re: Is there a measure to automate current and previous year from date column and do the calculation

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.

Community Support Team
Posts: 7,295
Registered: ‎05-02-2017

Re: Is there a measure to automate current and previous year from date column and do the calculation

Hi @Phaneendra,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Posts: 7,295
Registered: ‎05-02-2017

Re: Is there a measure to automate current and previous year from date column and do the calculation

Hi @Phaneendra,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.