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 attempting to allow users to select a value (Fiscal Period) and that will drive many other visual calculations. They will select this Period in a slicer.
In some calculations the period will be a max value, whereas others it will be the only value. So for that reason I cannot have them pick all the periods they want to look at. IE:
If they pick Period 2, it would show Sales in Period2, but show Inventory from Period 1 and 2 (Where Period < MAX(SelectedPeriod))
The issue is they COULD pick multiple periods. IE:
User picks Period 2 and Period 4, it would shows sales in P2 and P4, but show Inventory from P1-P4 (< MAX selected)
To accomplish this, I have a created table with no joins to any other table in the data and just has the periods that we are allowing users to select from. This works except for the situations where it would show only the specific Periods they select. I cannot get the calculations to compare a single value against a table of multiple values.
I did find this article CONTAINSX which gave me this calculation:
Year | Period | PeriodOffset | PeriodName | PeriodFilterKey | Period Exists |
2018 | 10 | -5 | Period 10 | 10BLANKPeriod 10BLANK | 0 |
2018 | 11 | -4 | Period 11 | 11BLANKPeriod 11BLANK | 0 |
2018 | 12 | -3 | Period 12 | 12BLANKPeriod 12BLANK | 0 |
2019 | 1 | -2 | Period 1 | 1BLANKPeriod 1BLANK | 1 |
2019 | 2 | -1 | Period 2 | 2BLANKPeriod 2BLANK | 1 |
2019 | 3 | 0 | Period 3 | 1BLANKPeriod 1BLANK | 0 |
Year | Period | PeriodOffset | PeriodName | PeriodFilterKey |
2019 | 1 | -2 | Period 1 | 1BLANKPeriod 1BLANK |
2019 | 2 | -1 | Period 2 | 2BLANKPeriod 2BLANK |
Solved! Go to Solution.
Hi @BSLATTER ,
Based on my test, you could refer yo below formula:
Measure = IF(CALCULATE(SUM(FiscalDateAggPeriod[Period]))<=CALCULATE(MAX('FiscalDateAggPeriod2'[Period]),FILTER('FiscalDateAggPeriod2','FiscalDateAggPeriod2'[PeriodName]=SELECTEDVALUE(FiscalDateAggPeriod2[PeriodName]))),1,0)
Result:
Regards,
Daniel He
Hi @BSLATTER ,
Based on my test, you could refer yo below formula:
Measure = IF(CALCULATE(SUM(FiscalDateAggPeriod[Period]))<=CALCULATE(MAX('FiscalDateAggPeriod2'[Period]),FILTER('FiscalDateAggPeriod2','FiscalDateAggPeriod2'[PeriodName]=SELECTEDVALUE(FiscalDateAggPeriod2[PeriodName]))),1,0)
Result:
Regards,
Daniel He
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |