Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
dhenders1833
Frequent Visitor

DATEADD With Inconsistent Date Range

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

dhenders1833_0-1716342230711.png

My date table: tbl_Date

dhenders1833_1-1716342277720.png

 

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):

Custom Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        tbl_Date,
        tbl_Date[Date] >= MIN(tbl_ReportData[Date]) &&
        tbl_Date[Date] <= MAX(tbl_ReportData[Date])
    )
)

 

This is the prior year measure along with the error I am receiving: 

PY Revenue = CALCULATE([Custom Sales],DATEADD(tbl_ReportData[Date],-365,DAY))
dhenders1833_2-1716342738333.png

 

Help! 🙂 

4 REPLIES 4
gmsamborn
Super User
Super User

Hi @dhenders1833 

 

Try using your date table in the DATEADD.

PY Revenue = CALCULATE( [Custom Sales], DATEADD( tbl_Date[Date], -365, DAY ) )


Proud to be a Super User!

daxformatter.com makes life EASIER!

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 #:

dhenders1833_0-1716382406945.png

With store #:

dhenders1833_1-1716382449354.png

 

Hi @dhenders1833 

 

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.



Proud to be a Super User!

daxformatter.com makes life EASIER!

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

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.