cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

DATEADD with GAPS

Hi guys, im struggling with Time functions  all day long. I have a date column with gaps, i mean, it doesnt have weekends for example, only data for workdays, so it skip some days, the problem is that i need to create a column to compare present data with last year data. The problem is always when a use DATEADD the results comes with gaps, i dont care if i am comparing 01/12/2020 with 01/13/2019, doesnt need to match exactly the day, what i need is continuous results, it is only an aproximation, but no matter what i do the results come with gaps, ive tried many different ways, could you help me?

 

Measure used to calculate the column:

Comissão ano anterior = CALCULATE(SUM('Comissão'[Comissão]),ALL('Comissão'), DATEADD('Comissão'[Data Liq.],-1,YEAR))
 
Capture.JPG
7 REPLIES 7
Highlighted
Super User IV
Super User IV

@paasxx - You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

@paasxx , if you want to have date add you need to have continuous dates. prefer a date calendar

Comissão ano anterior = CALCULATE(SUM('Comissão'[Comissão]),ALL('Comissão'), DATEADD('Date'[Data],-1,YEAR))

 

In date calendar, if you want to work with work days

https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

Some time you case use date in place of dateadd -https://community.powerbi.com/t5/Community-Blog/Date-as-Dateadd-Decoding-Date-and-Calendar-5-5-Power...

 

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Solution Sage
Solution Sage

DATEADD is a time-intel function and for correct results it needs a proper date table with a no-gap sequence of dates. Please create one and connect to your table. Then you can use DAX to give you what you need. If you use PBI and DAX the way they were intended to be used, you'll have an easy time creating good models.
Highlighted
Frequent Visitor

Hi , appreciate, i have tried that, but when a simply put a calendar table formated and etcetera, my data is discontinuous, and when a shift whit dateadd, it looks for date that doesnt necessarily exists in past year, so for example 01/12/2020 i have data, but dateadd dont find value for 01/12/2019, and the problem continues.

Highlighted

OK. Still, this is not a reason to not have a proper model and date table. However, when there's no corresponding date in the previous year, which day would you then want to return? It's not enough to say "i dont care if i am comparing 01/12/2020 with 01/13/2019, doesnt need to match exactly the day, what i need is continuous results," because programming is all about giving precise rules of calculation. So, in order for somebody to be able to give you an answer, you have to precisely define what the calculation must do when there's no corresponding date in the year before.
Highlighted
Frequent Visitor

Hi @daxer , you are perfectly right my friend, im newbie at power bi, but i know the rules of programming and it would be easy if it was in another language, but it isnt. What i was trying to say is that if i have a date x/x/2020 my problem would be solved just shifting 360 days ago and comparing with existing results that exists! but the function keeps jumping days that doesnt exists in a year before and present year, you have an idea of how to do it? Thanks ever since.

 

ps: im comparing values of 2 column with values 360 days before or a year before.

 

Capture.JPG

Highlighted

Maybe you just want to compare to the very last day last year that exists before the day you're on (shifted a year back)? This seems to me to be a reasonable idea...

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors