Forum Discussion

bimchahaha's avatar
bimchahaha
Microsoft Employee
6 years ago
Solved

sameperiodlast year aggregation issue

I am using Adventureworks as the sample data, I pull (import mode) dimdate, and factinternetsales to PBI desktop

TotalSales = SUM(FactInternetSales[SalesAmount])
TotalSalesLY = CALCULATE([TotalSales], SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))
 
And I can see TotalSalesLY when I drag TotalSales and FullDateAlternateKey, however the TotalSalesLY  is empty while aggregate at month,Qty,year level ( I believe the date hireachy is auto build) . Why is that ?
 
 
 
 
Need Help
Show and Tell
Tutorial Requests
  • AllisonKennedy's avatar
    AllisonKennedy
    6 years ago

    Have you set the date table yet? For time intelligence functions to work, you must use them with the date table date and it must be set as a date table, otherwise Power BI might make the wrong assumption for date table dates. 

     

    Click the ... on the date table and mark as date table and see if that helps.

     

    Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

     

    If you found this post helpful, please give Kudos.

    I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

    https://sites.google.com/site/allisonkennedycv

2 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago

    Have you set the date table yet? For time intelligence functions to work, you must use them with the date table date and it must be set as a date table, otherwise Power BI might make the wrong assumption for date table dates. 

     

    Click the ... on the date table and mark as date table and see if that helps.

     

    Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

     

    If you found this post helpful, please give Kudos.

    I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

    https://sites.google.com/site/allisonkennedycv