MTD when no month is selected on slicer

[arnabmit]

Hello good folks!

I have a simple query:
I have a MTD calculation based on a fact table, which works fine whenever a month is selected from the month slicer. However, the calculation always shows the MTD of Dec if no specific month is selected in the month slicer, and the slicer defaults to the [All] selection.

How do I force the MTD calculation to show the current month's data when no specific month is selected?

[Anonymous]

Hi @arnabmit ,

Please try below steps:

1. below is my test table

Table:

Table2: 

create with below dax formula

Table 2 =
ADDCOLUMNS (
    CALENDAR ( FIRSTDATE ( 'Table'[Date] ), LASTDATE ( 'Table'[Date] ) ),
    "Year", YEAR ( [Date] ),
    "Quarter", QUARTER ( [Date] ),
    "Month", MONTH ( [Date] )
)

2. create a measure with below dax formula

Measure =
VAR cur_year =
    SELECTEDVALUE ( 'Table 2'[Year] )
VAR cur_qtr =
    SELECTEDVALUE ( 'Table 2'[Quarter] )
VAR cur_month =
    SELECTEDVALUE ( 'Table 2'[Month] )
VAR today_date =
    TODAY ()
VAR _a =
    EOMONTH ( TODAY (), -1 ) + 1
RETURN
    SWITCH (
        TRUE (),
        NOT ( ISBLANK ( cur_year ) )
            && NOT ( ISBLANK ( cur_qtr ) ) && NOT ( ISBLANK ( cur_month ) ), TOTALMTD ( DISTINCTCOUNTNOBLANK ( 'Table'[Sale] ), 'Table 2'[Date] ),
        ISBLANK ( cur_month ),
            CALCULATE (
                DISTINCTCOUNTNOBLANK ( 'Table'[Sale] ),
                FILTER (
                    ALL ( 'Table 2' ),
                    'Table 2'[Date] >= _a
                        && 'Table 2'[Date] <= today_date
                )
            )
    )

3. add three slicer with "Table 2" fields, add a card visual with measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but please try something like below whether it suits your requirement.

FM MTD fix: =
VAR _todaymonthstart =
    EOMONTH ( TODAY (), -1 ) + 1
VAR _todaymonthend =
    EOMONTH ( TODAY (), 0 )
RETURN
    IF (
        HASONEVALUE ( CalendarTable[Month] ) && HASONEVALUE ( CalendarTable[Year] ),
        TOTALMTD (
            DISTINCTCOUNTNOBLANK ( Fact_FM_Count[Count key] ),
            CalendarTable[Date]
        ),
        CALCULATE (
            DISTINCTCOUNTNOBLANK ( Fact_FM_Count[Count key] ),
            FILTER (
                ALL ( CalendarTable ),
                CalendarTable[Date] >= _todaymonthstart
                    && CalendarTable[Date] <= _todaymonthend
            )
        )
    )

[arnabmit]

Thanks! This worked!