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 have a slicer that has the following column
Var CurrentMonth = CALCULATE(SUM(Table[Price]), FILTER(DimDate, MONTH(DimDate[FullDate]) = MONTH(TODAY()) && YEAR(DimDate[FullDate] = YEAR(TODAY() ))))
Var Budget = calculate(SUM(Budget[MonthlyBudget]))
Return CurrentMonth/Budget
but for somereason then my slicer will work only if something is selected. I want the card to show current year and month data if nothing is selected in the slicer and will dynamically change if something is selected in the slicer. How can I do that ?
Solved! Go to Solution.
Hi @SamOvermars ,
According to your description, in your formula, the below code only related to the current year and month but not the value selected in the slicer. Also, the Budget table will also filtered by the slicer, if you want to show current year and month data if nothing is selected in the slicer, you should also add filter in the variable Budget.
FILTER(DimDate, MONTH(DimDate[FullDate]) = MONTH(TODAY()) && YEAR(DimDate[FullDate] = YEAR(TODAY() ))
I create a sample.
Table:
DimDate table:
Budget table:
Here's my solution, create a measure.
Measure =
VAR _T1 =
FILTER (
DimDate,
MONTH ( DimDate[FullDate] ) = MONTH ( MAX ( 'DimDate'[FullDate] ) )
&& YEAR ( DimDate[FullDate] = YEAR ( MAX ( 'DimDate'[FullDate] ) ) )
)
VAR _T2 =
FILTER (
DimDate,
MONTH ( DimDate[FullDate] ) = MONTH ( TODAY () )
&& YEAR ( DimDate[FullDate] = YEAR ( TODAY () ) )
)
VAR _CurrentMonth =
IF (
ISFILTERED ( DimDate[MY] ),
CALCULATE ( SUM ( 'Table'[Price] ), _T1 ),
CALCULATE ( SUM ( 'Table'[Price] ), _T2 )
)
VAR _Budget =
IF (
ISFILTERED ( DimDate[MY] ),
CALCULATE ( SUM ( 'Budget'[MonthlyBudget] ), _T1 ),
CALCULATE ( SUM ( 'Budget'[MonthlyBudget] ), _T2 )
)
RETURN
_CurrentMonth / _Budget
Get the correct result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @SamOvermars ,
According to your description, in your formula, the below code only related to the current year and month but not the value selected in the slicer. Also, the Budget table will also filtered by the slicer, if you want to show current year and month data if nothing is selected in the slicer, you should also add filter in the variable Budget.
FILTER(DimDate, MONTH(DimDate[FullDate]) = MONTH(TODAY()) && YEAR(DimDate[FullDate] = YEAR(TODAY() ))
I create a sample.
Table:
DimDate table:
Budget table:
Here's my solution, create a measure.
Measure =
VAR _T1 =
FILTER (
DimDate,
MONTH ( DimDate[FullDate] ) = MONTH ( MAX ( 'DimDate'[FullDate] ) )
&& YEAR ( DimDate[FullDate] = YEAR ( MAX ( 'DimDate'[FullDate] ) ) )
)
VAR _T2 =
FILTER (
DimDate,
MONTH ( DimDate[FullDate] ) = MONTH ( TODAY () )
&& YEAR ( DimDate[FullDate] = YEAR ( TODAY () ) )
)
VAR _CurrentMonth =
IF (
ISFILTERED ( DimDate[MY] ),
CALCULATE ( SUM ( 'Table'[Price] ), _T1 ),
CALCULATE ( SUM ( 'Table'[Price] ), _T2 )
)
VAR _Budget =
IF (
ISFILTERED ( DimDate[MY] ),
CALCULATE ( SUM ( 'Budget'[MonthlyBudget] ), _T1 ),
CALCULATE ( SUM ( 'Budget'[MonthlyBudget] ), _T2 )
)
RETURN
_CurrentMonth / _Budget
Get the correct result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @SamOvermars ,
To apply the logic when something is filtered is good to use ISFILTERED and IF. So basically you should have your measure for latest period and for selection. Then you only need to use those two measures and combine it with functions above.
Some sample measure looks like this:
MyMeasure =
var _SelectedRevenue = SUM('Table'[REVENUE])
var _LatestMonth = CALCULATE(_SelectedRevenue,LASTDATE('Table'[DateColum]))
Return
IF(
ISFILTERED('DimColumn'[MMM-YYYY]),
_SelectedRevenue,
_LatestMonth
)
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 |
---|---|
48 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |