The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
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.
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.
@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
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.
@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
User | Count |
---|---|
11 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
23 | |
14 | |
13 | |
10 | |
8 |