Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |