topic Re: DATEADD with GAPS in DAX Commands and Tips

DATEADD with GAPS

Anonymous — Wed, 02 Sep 2020 20:43:18 GMT

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

Re: DATEADD with GAPS

Greg_Deckler — Wed, 02 Sep 2020 21:18:45 GMT

Anonymous - 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-TITHW/m-p/434008

Re: DATEADD with GAPS

amitchandak — Thu, 03 Sep 2020 10:17:54 GMT

Anonymous , 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-Calendar-4-5-Power/ba-p/1187766

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-BI-Turning/ba-p/1187827

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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Appreciate your Kudos.

Re: DATEADD with GAPS

Anonymous — Thu, 03 Sep 2020 10:57:25 GMT

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.

Re: DATEADD with GAPS

Anonymous — Thu, 03 Sep 2020 12:29:33 GMT

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.

Re: DATEADD with GAPS

Anonymous — Fri, 04 Sep 2020 11:00:36 GMT

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.

Re: DATEADD with GAPS

Anonymous — Fri, 04 Sep 2020 12:28:48 GMT

Hi Anonymous , 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.

Re: DATEADD with GAPS

Anonymous — Fri, 04 Sep 2020 13:03:41 GMT

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...