Create summary table with SAMEPERIODLASTYEAR

[Anonymous]

Hi,

I am trying to create Summary table with last year sales using SAMEPERIODLASTYEAR. but Sales PY is coming blank.

Invoice Summary = SUMMARIZE('Invoice Data','Invoice Data'[DIVISION_NAME],'Invoice Data'[PARENT_CUST],'Invoice DM'[FISCAL_YEAR_NAME],"Sales", [Sales1], "Sales PY", [Sales PY1])

Sales1 = SUM('Invoice Data'[SHIP_AMT])

Sales PY1 = CALCULATE([Sales1],SAMEPERIODLASTYEAR('Invoice DM'[DATE_VALUE]))

Thanks

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Thank you for your sample data, please modify Sales PY1 as below.

Sales PY1 = CALCULATE([Sales1],FILTER(ALLEXCEPT('Invoice Data','Invoice Data'[Parent_Customer]),'Invoice Data'[Invoice_Date].[Year]=SELECTEDVALUE('Invoice Data'[Invoice_Date].[Year])-1))

Pbix as attached.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[amitchandak]

For time intelligence function to work best, create a date table join table. then try

Sales PY1 = CALCULATE([Sales1],SAMEPERIODLASTYEAR('Date'[Date]))

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
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

@amitchandak  Invoice DM is my date table

[v-jayw-msft]

Hi @Anonymous ,

Please share some sample data and excpeted result to us if you don't have any Confidential Information.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

@v-jayw-msft please see sample data below, i have Invoice data and i expect invoice summary as shown below

invoice Data:

Division Name	Parent Customer	Invoice Date	ship amt
ABCD	A	1/1/2018	100
ABCD	A	1/1/2018	100
ABCD	A	1/1/2019	100
ABCD	A	1/2/2019	100
ABCD	A	1/3/2019	100
ABCD	A	1/4/2019	100
ABCD	A	1/5/2019	100
ABCD	A	1/1/2020	100
ABCD	A	1/2/2020	100
ABCD	B	1/1/2018	100
ABCD	B	1/2/2018	100
ABCD	B	1/1/2019	100
ABCD	B	1/2/2019	100
ABCD	B	1/1/2020	100
ABCD	C	1/1/2018	100
ABCD	C	1/2/2018	100
ABCD	C	1/1/2019	100
ABCD	C	1/2/2019	100
ABCD	C	1/1/2020	100

Invoice Summary

Division Name	Parent Customer	Invoice Year	sales	sales py
ABCD	A	2018	200	0
ABCD	A	2019	500	200
ABCD	A	2020	200	500
ABCD	B	2018	200	0
ABCD	B	2019	200	200
ABCD	B	2020	100	200
ABCD	C	2018	200	0
ABCD	C	2019	200	200
ABCD	C	2020	100	200

Thanks