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.
Hello,
I am new to Power BI and struggling to achieve the following.
I have contracts with a starting date and a termination date.
SO:
ContractA 2015-10-05 2016-07-17
ContractB 2016-04-30 2017-11-03
ContractC 2014-01-01 2018-07-17
Now I'd like to have one slicer to select a year. And with that one year I want to display three visuals that contain the following data:
Say, I select 2016 in the slicer
I want one visual to show all contracts that were active in 2016: Start date <2016-01-01 End date > 2016-12-31
=> Contract C
One with all contract started in 2016: 2016-01-01 < Start date >2016-12-31
=> Contract B
One with all contract terminated in 2016: 2016-01-01 < End date >2016-12-31
=> Contract A
How can I split the contracts between the three visuals?
So far I was unable to figure out how to filter the visuals in different ways based on input by a slicer.
The table does contain more data which is used to make the visual. I just limited it now to the data needed to show my filtering problem.
Hi ,
Create a date table have relationship between your table and the date table using start date.
Create another inactive relation between date table to your table using the end date.
Create 3 measures, each for the 3 scenario's
Measure1 = VAR X=CALCULATE(count('Table1'[Id]),FILTER(
'Table1',
('Table1'[Start_Date]>= FIRSTDATE('Date'[Date]) &&
'Table1'[End_date] <= LASTDATE('Date'[Date]))))
RETURN IF(ISBLANK(X),0,X)
Measure 2= count([Id])
Measure 3= calculate( count([ID]),userelationship(table1[end_date],date[date]))
Now create 3 visuals, using measure 1 for the criteria 1, measure 2 for the criteria 2, measure 3 for the criteria 3.
Hope this solves your concern.
Regards.
Hi @ArjanL.
Please review the following steps and get expected result.
1. Create a separate table including 'Year' column, and create a slicer based on Year column.
2. Create three measure according to your logical thinking.
active in 2016 = CALCULATE(FIRSTNONBLANK(Test[Contract],1),FILTER(Test,AND(Test[starting date]<DATE(SELECTEDVALUE('Year'[Year]),1,1),Test[termination date]>DATE(SELECTEDVALUE('Year'[Year]),12,31)))) started in 2016 = CALCULATE(FIRSTNONBLANK(Test[Contract],1),FILTER(Test,AND(Test[starting date]>DATE(SELECTEDVALUE('Year'[Year]),1,1),Test[starting date]<DATE(SELECTEDVALUE('Year'[Year]),12,31)))) end in 2016 = CALCULATE(FIRSTNONBLANK(Test[Contract],1),FILTER(Test,AND(Test[termination date]>DATE(SELECTEDVALUE('Year'[Year]),1,1),Test[termination date]<DATE(SELECTEDVALUE('Year'[Year]),12,31))))
3. Create three visuals to display the result, please see it in the following screenshot.
Please download the attachment file for more details.
Best Regards,
Angelia
This is close to what I need, but I bump into another problem.
This function allways returns the first contract that meets the requirements.Say both ContractC and ContractD are active in 2016, I only get ContractC as a return.
For testing I made a calculated column with the formula and a fixed year.
And with an AND statement I added Contract[No_]=EARLIER(Contract[No_])
Then I get the Contract No of the current line as return value, what is what I want.
So, I tried this in the measure, but then I get the error that EARLIER points to a row context that does not exist.
Is there another way to make sure I only get the current row as a return?
Hi @ArjanL,
Do you mind share your .pbix file for further analysis?
Thanks,
Angelia
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |