Forum Discussion
PY Calculation based on DATEADD field is Not Working
Hi,
I have a these 2 columns in my table:
Entry Date
Total Sales
I created a PY Entry date field using: DateAdd('Sales'[Entry Date], -364, DAY)
I am trying to create a column that shows the total sales for the PY based on the new date field.
I am using the column calculation: CALCULATE(SUM('Sales'[Total Sales]),DATEADD('Sales'[Entry Date], -364, DAY))
But it is showing blank values.
Any help?
All time calculations work best, when you have date dimesnion and you use that in time calculation. So first create that and then try\
CALCULATE(SUM('Sales'[Total Sales]),DATEADD('Date'[Date], -364, DAY)) Or CALCULATE(SUM('Sales'[Total Sales]),DATEADD('Date'[Date], -1, Year)) If needed CALCULATE(SUM('Sales'[Total Sales]),DATEADD('Date'[Date], -364, DAY),all(Date'[Date])) Or CALCULATE(SUM('Sales'[Total Sales]),DATEADD('Date'[Date], -1, Year),all(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 -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
3 Replies
- amitchandak
Super User
All time calculations work best, when you have date dimesnion and you use that in time calculation. So first create that and then try\
CALCULATE(SUM('Sales'[Total Sales]),DATEADD('Date'[Date], -364, DAY)) Or CALCULATE(SUM('Sales'[Total Sales]),DATEADD('Date'[Date], -1, Year)) If needed CALCULATE(SUM('Sales'[Total Sales]),DATEADD('Date'[Date], -364, DAY),all(Date'[Date])) Or CALCULATE(SUM('Sales'[Total Sales]),DATEADD('Date'[Date], -1, Year),all(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 -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601 
nhowardResolver I
Anonymous
Sounds like you want to know the sales amount for the same date last year. I am assuming you have entries that go back that far, and that there will be some where 0 is correct. In fact for the first year of your data, blank is correct. It sounds like you don't have a master calendar table (lists every possible date you could likely use); these are best practice when needing to calculate by date range, and work best for filtering by date range.
In what kind of visual are you putting this measure? The filter context of the formala makes a big difference to how it works.
Your formula looks okay. In fact I have replicated a data table with only date and amount, and used your formula just fine; in both a table visual and a matrix visual.
It might be helpful if you share you data or pbix file.