cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

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

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

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

Proud to be a Super User!

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

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

Proud to be a Super User!

Highlighted
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
Solution Sage
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.

Highlighted
Solution Sage
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...

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors