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.
Hi,
I have a "Date" dimension and another table in which my data looks like :-
WorkID | StartDate | EndDate | Type |
1 | 1/1/2014 | 1/5/2017 | Rejected |
2 | 7/7/2015 | Active | |
3 | 4/4/2016 | 5/7/2017 | Closed |
4 | 2/9/2017 | Active |
Now, the problem is that I need to create a visual that displays the count(workid) where startdate<currentmonth and enddate>current month. The current month here refers to the month I am looking at in the visual. If I create a relationship of Startdate or EndDate with 'Date'[Date], it messes up the entire calculation. Any idea how do I tackle this?
Solved! Go to Solution.
Hey,
basically what you are looking for is called the "Event in Progress".
Please be aware, that solutions for these kind of questions require that the Calendar table is not related to the fact table.
Here you will find a pbix file that contains a little example, based on your sample data it shows 3 rows valie for the selected month "2016-06":
I've created a measure "Active WorkID"
Active WorkID = SUMX( 'Fact' , var theEnd = IF(ISBLANK('Fact'[EndDate]), DATE(9999,12,31), 'Fact'[EndDate]) return IF(AND('Fact'[StartDate] < [Min Selected Date], theEnd > [Max Selected Date]) ,1 ,BLANK() ) )
that I'm using with the card visual. this measure returns the value 1 one for each WorkID that is "active". This measure can also be used in the Visual level filter section of a visual.
Hope this is what you are looking for
Regards
Tom
Hey,
so you are saying
Month('startdate' + 45) = Month(Selected)
Left side
2014-07-01 + 45days = 2014-08-15 --> Month = 8
Right side
2018-08 (Selected Month) = Month(...) = 8
this comparison equals to true?
Not sure what you mean if you are saying you can't see trends, trends of what
I would consider this a trend.
I have to admit that I have my difficulties to understand your requirements, sorry for that
Regards
Tom
Hey,
basically what you are looking for is called the "Event in Progress".
Please be aware, that solutions for these kind of questions require that the Calendar table is not related to the fact table.
Here you will find a pbix file that contains a little example, based on your sample data it shows 3 rows valie for the selected month "2016-06":
I've created a measure "Active WorkID"
Active WorkID = SUMX( 'Fact' , var theEnd = IF(ISBLANK('Fact'[EndDate]), DATE(9999,12,31), 'Fact'[EndDate]) return IF(AND('Fact'[StartDate] < [Min Selected Date], theEnd > [Max Selected Date]) ,1 ,BLANK() ) )
that I'm using with the card visual. this measure returns the value 1 one for each WorkID that is "active". This measure can also be used in the Visual level filter section of a visual.
Hope this is what you are looking for
Regards
Tom
Hi Tom,
This is very close to what I need. However, how do I include this condition?
enddate>createddate+45 && createddate+45 (the month) = currentmonth (selected in filter with ability to select multiple months)
OR
enddate is null && currentmonth>=createdate+45 (the month)
If either of them satisfies, we should have the value.
Hey,
I have no idea
Regards
Tom
My apologies for the confusion.
1. The createddate is actually the StartDate.
2. Right, +45 means addition of 45 days to StartDate.
3. Right.
Hey,
can you please provide examples assuming that 2016-05 and 2016-09 is selected in the sliccer. I still do not understand what you mean by
createddate+45 (the month) = currentmonth (selected in filter with ability to select multiple months)
Regards
Tom
I mean that the pbix you have only allows me to choose a month & year. How can I see trend in this case? Also,
createddate+45 (the month) = currentmonth (selected in filter with ability to select multiple months)
This means Month(startdate+45) = month(the month in visual).
Hey,
so you are saying
Month('startdate' + 45) = Month(Selected)
Left side
2014-07-01 + 45days = 2014-08-15 --> Month = 8
Right side
2018-08 (Selected Month) = Month(...) = 8
this comparison equals to true?
Not sure what you mean if you are saying you can't see trends, trends of what
I would consider this a trend.
I have to admit that I have my difficulties to understand your requirements, sorry for that
Regards
Tom
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 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |