Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hopefully this will be straightforward (but may not be!). I wish to create a measure to calculate the length in weeks of a previous school term or if its the current one, the number of weeks elapsed so far in the term.
I have a table with term dates in it - start and finish and tried this:
Solved! Go to Solution.
Absolutely!
In a measure:
01: Completed Weeks = var _MinDate = MIN(Table1[Start]) var _MaxDate = MAX(Table1[End]) var _Formula = IF(TODAY() > _MaxDate, DATEDIFF(_MinDate, _MaxDate, WEEK), DATEDIFF(_MinDate, TODAY(), WEEK)) return IF(_Formula <= 0, 0, _Formula)
Hi buddy,
i may be wrong, but, confirme if your columns are on date format.
Also, can you give some example so i can understand better, something like:
Date value
01/02/2019 30
Any questions, ask 😉
This is the table with the dates in date fomat
Many thanks for getting back to me
So, buddy,
So you can try this out:
Or maybe this:
this is realy hard to know without the real scenario, but dosen't hurt give a try.
Any questions, ask 😉
Thanks for trying, not quite sure which tables you are referring to. The problem is entriely based on the table I've shown so when I select spring 2019 it returns the number of weeks between the 7/1 and the 30/3. If I select summer 2019 it returns 2 as we are two whole weeks into the term.
thanks again
Try this in a calculated column:
Completed Weeks = var _Formula = IF(TODAY() > Table1[End], DATEDIFF(Table1[Start], Table1[End], WEEK), DATEDIFF(Table1[Start], TODAY(), WEEK)) return IF(_Formula <= 0, 0, _Formula)
Great - that does the trick. Is there any way to make it a measure as I would like to add it to a table and use it to calculate various other measures?
Absolutely!
In a measure:
01: Completed Weeks = var _MinDate = MIN(Table1[Start]) var _MaxDate = MAX(Table1[End]) var _Formula = IF(TODAY() > _MaxDate, DATEDIFF(_MinDate, _MaxDate, WEEK), DATEDIFF(_MinDate, TODAY(), WEEK)) return IF(_Formula <= 0, 0, _Formula)
Great - many thanks for your help to both of you
Just taking a deeper look at my solution and there's an incorrect total of completed weeks. You'll need to create a new measure to get the correct total:
02: Correct Total = SUMX(SUMMARIZE(Table1, Table1[Term Name]), [01: Completed Weeks])
I see it. It doesn't matter if you filter by term but if not, it will include the holidays (the weeks between terms). Bring on the summer!
Thanks again
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |