Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I'm new to PowerBI and the DAX syntax.
I have 2 tables (Sprints and WorkItems). All date columns are formatted as Date
Sprints table: (columns StartDate, FinishDate and SprintNumber)
Solved! Go to Solution.
I don't think you've told us all about the model... I suspect there are relationships between the two tables based on the date fields.
Try this
CreatedInSprint = var __date = WorkItems[fields_SystemCreatedDate] return MAXX( FILTER( Sprints; AND( Sprints[attributes_startDate] <= __date, __date <= Sprints[attributes_finishDate] ) ), Sprints[SprintNo] )
This should work correctly on the assumption that there is always at most one sprint returned by the logical condition in FILTER. If there happen to be many, then the maximum SprintNo will be returned.
Best
Darek
I’m trying to create a formula to show the QBEstimate.Monthlyfee for the righg billing period
My qBEstimate Table has a QBEstimate.EsStartDate, QBEstimate.EsEndDate and monthly fee. I’m trying to create a matrix to show the fee by TDate.Billing Month
I know the problem is in my relationships but I can’t set the set QBEstimate.EsStartDate and QBEstimate.EsEndDate to the TDate.Billing Month
This is my measure – it returns te right values for some months but not all.
DAX measure
MSSMonthlyFees =
CALCULATE(
SUM(QBEstimate[MonthlyFee]),
FILTER(QBEstimate,
QBEstimate[EsStartDate] <= min(TDate[Billing Month]) &&
QBEstimate[EsEndDate] >= max(TDate[Billing Month])
)
)
All help welcome
Thank you
TDATE Table
TDate = ADDCOLUMNS(
CALENDAR(date(2021,1,1), date(2022,12,31)),
"Month", FORMAT([Date],"mmm YY"),
"MonthOrder", MONTH([Date]),
"Year",YEAR([Date]),
"Week", WEEKNUM([Date]),
"WeekYear", concatenate(YEAR([Date]),WEEKNUM([Date])),
"Billing Month",
VAR DayNumber = WEEKDAY ( [Date], 1 ) RETURN IF(DayNumber = 7,[Date] - 1, [Date] + 6 - DayNumber)
)
QBEstimate Table
Id | CustomerRef_Value | EsStartDate | ESEndDate | MonthlyFee |
17563 | 1252 | 4/21/2022 | 10/22/2022 | $9,900.00 |
17558 | 1247 | 4/1/2022 | 4/1/2023 | $21,991.67 |
17494 | 1185 | 2/13/2022 | 2/13/2023 | $19,227.67 |
17531 | 1216 | 8/21/2021 | 8/19/2022 | $25,695.00 |
17530 | 1215 | 8/19/2021 | 8/19/2022 | $10,075.00 |
17492 | 1183 | 7/30/2021 | 10/22/2022 | $4,070.30 |
17518 | 1204 | 7/1/2021 | 5/1/2022 | $20,720.74 |
17487 | 1159 | 6/30/2021 | 6/30/2022 | $35,000.00 |
17523 | 1165 | 8/22/2020 | 10/22/2022 | $15,578.81 |
Hi @Anonymous, would you be able to help with a similar question. I have a delivery note date that i wish to determine is within my period dates, (From and To) which are in the same table. I have tried the regular DAX expression of and([DELNOTE_DATE]>=[date_from],[DELNOTE_DATE]<=[date_to]) but i just get all true results. I have hard coded the date using and([DELNOTE_DATE]>=date(2021,11,22),[DELNOTE_DATE]<=date(2021,12,26)) and this works perfectly? AlI columns are set as date. Any help/guidance would be appreciated.
I don't think you've told us all about the model... I suspect there are relationships between the two tables based on the date fields.
Try this
CreatedInSprint = var __date = WorkItems[fields_SystemCreatedDate] return MAXX( FILTER( Sprints; AND( Sprints[attributes_startDate] <= __date, __date <= Sprints[attributes_finishDate] ) ), Sprints[SprintNo] )
This should work correctly on the assumption that there is always at most one sprint returned by the logical condition in FILTER. If there happen to be many, then the maximum SprintNo will be returned.
Best
Darek
Hi @Anonymous, would you be able to help with a similar question. I have a table in which i am looking to return true/false for a date which comes after the Date_from column and before the Date_until column date. I have tried a regular DAX expression of
InRangeDate = and(DELNOTES[DELNOTE_DATE]>=DELNOTES[DATE_FROM],DELNOTES[DELNOTE_DATE]<=DELNOTES[DATE_UNTIL]) but this only returns TRUE in all instances. I have then tried to hard code the date for a given accounting period using this expression
Hi Darek
Thank you for your rapid reply. Your assumption was correct and it works out of the box.
Just to follow-up on the model.
The following relationships exist (between Dates and Sprints) and (between Dates and WorkItems)
From date in Dates to attributes_startDate in Sprints (1:*) and (cross filter direction: Both)
From date in Dates to attributes_finishDate in Sprints (1:*) and (cross filter direction: Both)
From date in Dates to fields_SystemCreatedDate in WorkItems (1:*) and (cross filter direction: Both)
Best regards
Martin
I want to warn you:
Be extremely careful with a model that has both-ways cross-filtering enabled. This is very DANGEROUS and you may end up calculating things you won't understand. The best people in the world of DAX say that both-ways cross-filtering should be enabled IF AND ONLY IF it's strictly necessary and when you understand all the consequences. I'd advise that you revise your model and remove cross-filtering as much as possible. If the model becomes at one point ambiguous (because, for instance, you add some tables to it and create relationships) and the engine does not detect it (which is not uncommon), then you'll be in deep trouble.
You've been warned.
Best
Darek
I see, thank you for the insights on this topic.
I will change my model with this in mind.
Best regards
Martin
Hi @Anonymous,
Could you pls advise for a similar issue?
1st table is a typical Calendar table (column Date is of interest).
2nd table has the following columns: Opportunity ID, Opp Start Date, Opp Close Date, Opp value.
I want to get the cumulative value of valid Opportunities at 'Calendar'[Date] hierarchy. An Opportunity is considered valid when Date is between the Opp Start Date and the Opp Close Date.
Thank you for your effort.
// Calendar can be connected via Date
// to any or both of the columns in Opportunities.
// This does not matter for this calculation.
// If there are any relationships, the
// CROSSFILTER function will remove the
// relationships. If there are no relationships
// you might need to remove the function from
// the code. Assumption is that each opportunity
// has a start date and a close date that's not
// blank. If an opportunity is still valid today
// the end date will be, say, coded as 3000-01-01.
// No blanks allowed. BLANKS will complicate the
// code and make it slower.
[Valid Opp Count] =
var __dateSelected = SELECTEDVALUE( Calendar[Date] )
var __isDateDirectlyFiltered = ISFILTERED( Calendar[Date] )
var __count =
CALCULATE(
Opportunities,
Opportunities[Start Date] <= __dateSelected,
__dateSelected <= Opportunities[End Date]
// If you allow End Date to be blank, then you
// have to add this to the above expression
// || ISBLANK( Opportunities[End Date] )
// Out of the two select the correct one
// or remove them if there is no relationship
// from Date to these columns.
CROSSFILTER( 'Calendar'[Date], Opportunities[Start Date], NONE ),
CROSSFILTER( 'Calendar'[Date], Opportunities[End Date], NONE )
)
return
if( __isDateDirectlyFiltered, __count )
Best
D
Thank you @Anonymous for your ideas. I've done some testing but I didn't manage to get any results.
Do you mind taking a look at the sample pbix file here?
https://drive.google.com/open?id=1nZGsdNwTTDNcyrjDmnPJxdl589M1C5fk
Hi there. I've done some work on this but I'm too tired right now to make it the way it should be. I've noticed that, for instance, the calendar does not handle missing dates properly. Dates should be handled in such a way that when there's no date, BLANK is not left in such a field but a dedicated date (e.g., 3000-01-01) as assigned to it and this date is present in the Calendar as well. The real Date field in the Calendar should be hidden and a date-like text should be presented to the user. The special dates that handle missing dates should have a label like Unknown or maybe 'Not Started' or 'Not Finished'... Something of this kind. But BLANKS should be avoided as much as possible because they make calculations not only more complex but also slower. Having said all that... I attached a file with what you wanted. Please take a look at how I handled the opportunities that do not have a start date. If an opportunity does not have a start date, it means this opportunity does not exist.
Best
D
Great advice @Anonymous, thank you!
I made all necessary changes as far as Calendar is concerned and results are verified on day basis.
I've noticed though that Date Hierarchy is not available to use in the visual. Is filtering inside the '# Opportunities' measure responsible for that?