The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi All-
I have searched for days on YouTube and the web trying to find solve for the folllowing issue, and hope someone here can help. I have the following dataset that includes sales by store for the last 3 years. When I try to calcuate prior year sales using the DATEADD function, I receive the following error. I believe it is due to the fact that the dataset includes new stores that do not have a prior year to reference. Has anyone found a solution for how to get the DATEADD function to work in this case? I have found a workaround by using the PARALLELPERIOD function when calcualting MTD, QTD, and YTD, however I want to include a custom date filter in my slicer, and need to utilize DATEADD so that it will calculate exactly 365 back from the specified date range.
My fact table: tbl_ReportData
My date table: tbl_Date
I created separate a custom sales measure (in addition to MTD, QTD, and YTD measures I have also created. I have a disconnected table that I am using with the slicer):
This is the prior year measure along with the error I am receiving:
Help! 🙂
Try using your date table in the DATEADD.
PY Revenue = CALCULATE( [Custom Sales], DATEADD( tbl_Date[Date], -365, DAY ) )
Thanks for the recommendation, however that yields the same error as well. It will allow me to pull into the visual alone, however when I pull in store number it then errors out.
Without store #:
With store #:
Can you replicate this problem with a small sample pbix? (I've tried a couple of simple examples and mine seem to work as expected.)
Also, can you double-check the error for your most recent example? The previous error had to do with date tables in particular.
Hi-
Please see example at link below. Essentially I am unable to get the PY measure to calculate properly. If I use PARALLELPERIOD, it calculates however the amount is incorrect. If I use DATEADD, it will not allow me to pull into the visual.
I believe the issue it due to the fact that Store 202 does not have data for Apr 2024. However in my actual dataset, I want the ability to still be able to calculate PY in spite of this, and I want to have a separate slicer that will allow the user to toggle between comp, non comp, and all stores.
https://drive.google.com/file/d/1BZrWk8RtIcTZBRL5JiDrnO8cVCYT4O9t/view?usp=drive_link
User | Count |
---|---|
26 | |
10 | |
8 | |
6 | |
5 |
User | Count |
---|---|
33 | |
13 | |
12 | |
9 | |
7 |