cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
snifer Member
Member

calculation of same period but prev year

in my table, I got a column id column date and column net profit

if I make this measure 

Profit prev year2 = CALCULATE (SUM(ViewProjectProfit[NetProfit]), SAMEPERIODLASTYEAR( ViewProjectProfit[dtAccounting] ) )

 

but this measure give just the total that is correct but doesn't make the monthly split as for the other columnCapture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: calculation of same period but prev year

hi, @snifer

SAMEPERIODLASTYEAR Function is Time Intelligence Functions it needs a date table

and for you formula, [dtAccounting] is from basic data table so it will leads this issue.

You could do these as below to solve it.

Step1:

Add a date table and create the relationship with basic data table

1.JPG

Step2:

Create a new Profit prev year2 by this formula

new Profit prev year = CALCULATE (SUM(ViewProjectProfit[NetProfit]), SAMEPERIODLASTYEAR('Date'[Date]))

Step3:

Drag the date field from date table instead of ViewProjectProfit[dtAccounting] into visual

2.JPG

here is demo pbix, please try it.

https://www.dropbox.com/s/l91yzyyu0cqu4l1/calculation%20of%20same%20period%20but%20prev%20year.pbix?...

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 REPLIES 10
AkhilAshok Established Member
Established Member

Re: calculation of same period but prev year

For SAMEPERIODLASTYEAR to work properly, usualy it needs a Date Table. You can try creating a Date dimension table and create a relationship between dtAccounting and Date. Afterwards, you can try SAMEPERIODLASTYEAR(Date[Date]).

rafaelmpsantos Established Member
Established Member

Re: calculation of same period but prev year

Year and Month Columns, are comming from what table?

Community Support Team
Community Support Team

Re: calculation of same period but prev year

hi, @snifer

SAMEPERIODLASTYEAR Function is Time Intelligence Functions it needs a date table

and for you formula, [dtAccounting] is from basic data table so it will leads this issue.

You could do these as below to solve it.

Step1:

Add a date table and create the relationship with basic data table

1.JPG

Step2:

Create a new Profit prev year2 by this formula

new Profit prev year = CALCULATE (SUM(ViewProjectProfit[NetProfit]), SAMEPERIODLASTYEAR('Date'[Date]))

Step3:

Drag the date field from date table instead of ViewProjectProfit[dtAccounting] into visual

2.JPG

here is demo pbix, please try it.

https://www.dropbox.com/s/l91yzyyu0cqu4l1/calculation%20of%20same%20period%20but%20prev%20year.pbix?...

 

Best Regards,

Lin

 

 

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

Re: calculation of same period but prev year

thank you now working fine, one last question how I can display the % simbol near the value on the %variazione columCapture.PNG

Community Support Team
Community Support Team

Re: calculation of same period but prev year

HI, @snifer

After my test, you could achieve it like this

Add "%"&   in your formula

for example

Measure = "%"&IF(CALCULATE(MAX(Project[Report As At ]),ALLSELECTED(Project))=MAX(Project[Report As At ]),1,2)

10.JPGbefore11.JPGafter

by the way, if you do this, the format of this measure will be Text. 

 

Best Regards,

Lin

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

Re: calculation of same period but prev year

adding the "%"& to the formula change my column in text that is not good for me

Community Support Team
Community Support Team

Re: calculation of same period but prev year

hi, @snifer

The format of "%+numerical value" can't be the numeric type, it only can be text type.

You can format it into "numerical value+%" or format it into "%+numerical value" as a final result。

If you continue to use it for later calculations, you could copy this measure, One is %variazione  for this visual and another is variazione for later calculations.

 

Best Regards,

Lin

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

Re: calculation of same period but prev year

i try to add +% but i got erro of offeset

Community Support Team
Community Support Team

Re: calculation of same period but prev year

hi, @snifer

I have written three different formulas of measure for you refer to.

4.JPG

and the keep numerical Measure is the numeric type and can be set by: select the measure->click modeling->formating

5.JPG

and other two measure are text type

%Measure = "%"&IF(CALCULATE(MAX(Project[Report As At ]),ALLSELECTED(Project))=MAX(Project[Report As At ]),1,2)



Measure% = IF(CALCULATE(MAX(Project[Report As At ]),ALLSELECTED(Project))=MAX(Project[Report As At ]),1,2)&"%"

here is pbix, please try it.

https://www.dropbox.com/s/l91yzyyu0cqu4l1/calculation%20of%20same%20period%20but%20prev%20year.pbix?...

 

Best Regards,
Lin

 

 

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