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