Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
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-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
this worked! Thanks
CALCULATE(SUM('Sales'[Total Sales]),DATEADD('Date'[Date], -364, DAY),all(Date'[Date]))
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |