Dax measure to calculate current year if no filter is selected

[SamOvermars]

Hello, 

I have a slicer that has the following column 

MY = FORMAT(DimDate[FullDate], "mmm-yy")

It will filter the dashboard based on the month and year.

I need to create a card that will show the Current month to budget and the month will be changed based on the above slicer.

For example if I pick July-21 it will compare the july21 data to Julys budget.

so far I created this measure for the card

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 ?

[v-yanjiang-msft · Accepted 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.

[Migasuke]

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
)