Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
snifer
Post Patron
Post Patron

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
v-lili6-msft
Community Support
Community Support

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.

View solution in original post

10 REPLIES 10
v-lili6-msft
Community Support
Community Support

hi, @snifer

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

 

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.
v-lili6-msft
Community Support
Community Support

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.

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

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)

beforebeforeafterafter

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.

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

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.

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

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.

Year and Month Columns, are comming from what table?

AkhilAshok
Solution Sage
Solution Sage

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]).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.