cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nmhung49
Helper I
Helper I

Caculate same period last year

I have Data table with date 01/01/2020 to 05/31/2021 and DimDate with date 01/01/2020 to 12/31/2021

When I create Dax Same Period Last Year for result 98 is incorrect. It is correct 13

Please help me the formula in this case

 

Thanks

 

nmhung49_0-1628491509219.png

nmhung49_0-1628559010821.png

 

 

Link File

 

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Thejeswar
Memorable Member
Memorable Member

Hi @nmhung49 ,

Change the SPLY formula as follows. Also I am not really sure, why you need a separte Date Dimension Table for this functionality. Change the formula to use Date from the same table as given below

 

SPLY = CALCULATE(SUM(Data[Sale]),SAMEPERIODLASTYEAR(Data[Date]))
 
This will change the Output from 98 to 13.
 
Why do you expect to see 11..which the Sales for Same period last year is 13
PFB screengrab
Thejeswar_0-1628518198480.png

 

@ryan_mayu @amitchandak 

Sorry It is show 13, I separte DimDate Table because this is the primary key table that is related to other tables I use this table to control year, month, day

@nmhung49 

Pls try this

SPLY = 
VAR _MAX=MAX('Table'[Date])
RETURN CALCULATE(SUM('Table'[Sale]),FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(_MAX)-1&&MONTH('Table'[Date])<=MONTH(_MAX)))

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

@nmhung49 

why last year is 11? from jan to May is 13. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I'm sorry show 13 not 11, I want to Card 1 st show 30 and Card 2nd show 13 with (Picture #1) because Actual Data have Date 01/01/2020 to 31/05/2021 but DimDate have Date 01/01/2020 to 31/12/2021

@nmhung49 

pls try this

Measure = 
VAR _max=max('Table'[Date])
return sumx(FILTER('Table','Table'[Date]=_max),'Table'[sales])




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

@nmhung49 , I check out total of year 2020 is 98. As you have seleced year, That will give you year total.

 

Also marked DimDate table as date table(option on right on table) and createcolumsn for Month, Yeat etc

 

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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Thanks for you repply

If slicer selected 2021 and Month not selected. I want Card Current Year show 30 and Card Same Period Last Year show 11

Thanks 

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!