12-06-2018 01:48 PM
Fantastic! Thank you for all the help. By giving the treatments ID i can use exactly the same approach for all parts of the bigger dataset that I have. Again, thank you!
12-12-2018 03:48 AM
Hi @AlB, thank you for all the previous help. From your formula i have managed to build significant improvments on my dataprocessing. However, could I ask you to look into one more thing?
I have expanded the dataset from just 2018 to also include previous years. Naturally this bring another variable to the "treatmentchange" collumn as this just seperates per week. So if a treatment is started in closely realted week in a previous year it isn't picked up.
The sheet is exactly the same as you allready have downloaded, execpt that the "År" collumn now also contains 2017 ,2016 etc.
12-12-2018 08:39 AM
What first comes to mind, and in order to reutilise to the max the approach we have already have, is to create a new "global" week number, i.e. that gives the week number not for a year but for the whole period you are considering. For instance, imagine your first year is 2016:
- Week 1 of 2016 is your "beginning of time" and would be assigned GlobalWeekNum=1.
- Week number 13 of 2017 would be assigned GlobalWeekNum=NumberOfWeeksIn2016+13=52+13=65
And so on...
so the GlobalWeekNumber doesn't go back to one at the change of the year like the standard week number does, but just continues on.
If you have an extra column with that global week num you would use it instead of [Uke] in the previous code and I believe it should work . Give it a good thought. I'm just saying this quickly.
Be careful with years with 53 weeks and especially with the ALLEXCEPT( ) we had in the code as it might need some changes.
CALCULATE ( MAX ( Table1[Uke] ); Table1[Uke] < CurrentWeek; ALLEXCEPT ( Table1; Table1[Lokalitetsnummer]) )
12-12-2018 09:52 AM
@AlBI like your thought, and i agree that it might work, and i don't see any hickups down the line of where I want to go with my data.
As you probably well have realized i'm not exactly fluent in DAX, so do you have a suggested code for the Globalweek collumn?
12-12-2018 10:19 AM - edited 12-12-2018 10:22 AM
I'm a bit busy right now. You give it a try and if it doesn't work post what you have and we'll take a look.
It should be possible with code quite similar to what you would use in excel.
12-12-2018 01:21 PM
Thank you for the pointers,
12-12-2018 02:36 PM
By the way, not sure if you know, you can use the "insert code" button to paste code keeping the formatting here. It's the one with the </> sign, next to the emoticon in the editor.
A tool you might find interesting is the DAX formatter by the Italian gurus. It formats/indents your code nicely, making it more readable and thus more maintainable.
12-13-2018 12:52 AM
Haha, AIB i'm not going to claim credit for those lines of code (yet). After trying for a while i got some help from someone more experienced. I'm a veterinarian, not a computer scientist so my baseline DAX knowledge is low. However, with the help of yourself and the others in this great community i'm quickly progressing. Allready my Power BI sheets give me tenfold the information my old Excel sheets do. Everytime one of you help me with a line of code, i analyze it to understand why it is working.
Again, thank you!
And for the future, any pointers on how to progress is much appreciated!