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 trying to get some dynamic filtering going on a Power Bi model im working on. I have a list of contracts and their start and end dates. I want the user to be able to pick a month (on the dashboard) and it will show them on the dashboard the contracts that are live on these dates.
I did manage to get it working for if the contract start date is the same as said month but im having trouble with the measure formula to include contracts that are live.
To sepcify i need the contracts to show where StartDate<SaidMonth and EndDate> Said month.
Does anyone have a solution?
Thanks
Solved! Go to Solution.
Thansk for the help guys, My collegue managed to find a solution here:
https://community.powerbi.com/t5/Desktop/Filtering-within-a-date-range/td-p/227767
I will try and test your solutions out when i get chance. Always good to know multiple ways to do things.
In Home->Enter Date, enter month value from 1 to 12, then in this table, create a measure
selectmonth = SELECTEDVALUE(Table1[month])
Then In the orginal table,create calculated columns
startmonth = MONTH([start date]) endmonth = MONTH([end date])
Next create a measure
Measure = IF([selectmonth]<=MAX([endmonth])&&[selectmonth]>=MAX([startmonth]),1,0)
Finally, add this measure to the Visual level Filter
Best Regards
Maggie
Thansk for the help guys, My collegue managed to find a solution here:
https://community.powerbi.com/t5/Desktop/Filtering-within-a-date-range/td-p/227767
I will try and test your solutions out when i get chance. Always good to know multiple ways to do things.
Try the below logic,
Num of Contracts = CALCULATE(COUNT([Contracts ID]),FILTER(TableName,StartDate<SaidMonth && EndDate> Said month))
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 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |