Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IHam
Helper III
Helper III

return the earliest of two dates

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:

Calculating Weeks = calculate(DATEDIFF(
MIN('Term Dates'[Start Date]),MIN('Term Dates'[End Date],Today()),
WEEK)
)
I then use a slicer to select the term.
The part in bold seems to be the problem. It needs to select the earliest of either the end of term (this would only be for previous terms) or the current date (for the current term, if selcted by the slicer). 
If I remove the ,Today() part it works (but doesn't do what I want for the current term)
 
The error message I get is:
"A single value for column 'End Date' in table 'Term Dates' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
 
many thanks
1 ACCEPTED SOLUTION
hnguy71
Memorable Member
Memorable Member

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)

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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 😉

T.png

This is the table with the dates in date fomat

Many thanks for getting back to me

Anonymous
Not applicable

So, buddy,

 

So you can try this out: 

 

Calculating Week = DATEDIFF(Table2[Start Date];Table2[End Date];WEEK)

 

image.png

 

Or maybe this:

this is realy hard to know without the real scenario, but dosen't hurt give a try.

Calculating Weeks 2 = CALCULATE(DATEDIFF(MIN(Table2[Start Date]);IF(HASONEFILTER(Table1[ColumnThatisUsedAsFilter])=TODAY();TODAY();MIN(Table2[End Date]));WEEK))

 

 

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

hnguy71
Memorable Member
Memorable Member

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)

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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?

hnguy71
Memorable Member
Memorable Member

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)

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Great - many thanks for your help to both of you

hnguy71
Memorable Member
Memorable Member

@IHam 

 

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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.