The formula gives BLANK value for MTD

[cj_oat]

Hi guys,

I'm trying to create MTD Card with below formula but it only gives me blank value. it will only shows value when I select specific month in the filter.

However, I want it to show the MTD value based on the selected month (if any month selected in the filter) or if I select ALL/Unselect ALL months, I want it to show MTD value based on latest month.

__TY MTD (Net Sales) =

CALCULATE(SUM('Sales Report'[Net Sales]) , FILTER(ALL('Calendar Table (Date)'),'Calendar Table (Date)'[Date]<=MAX('Calendar Table (Date)'[Date])),'Calendar Table (Date)'[Month]=MAX('Calendar Table (Date)'[Month]))

[Bibiano_Geraldo]

Hi @cj_oat 
Your DAX formula is designed to calculate the Month-to-Date (MTD) Net Sales but appears to be returning blank because it doesn't correctly handle the case where no specific month is selected. To fix this and meet your requirements, you need to add logic to determine the latest month when no filter is applied.

Try this updated measure:

__TY MTD (Net Sales) =
VAR SelectedMonth =
    MAX('Calendar Table (Date)'[Month]) -- Get the selected month, if any
VAR LatestMonth =
    MAXX(ALL('Calendar Table (Date)'), 'Calendar Table (Date)'[Month]) -- Get the latest month from the calendar table
VAR MonthToUse =
    IF(ISBLANK(SelectedMonth), LatestMonth, SelectedMonth) -- Use selected month or fallback to latest month
RETURN
    CALCULATE(
        SUM('Sales Report'[Net Sales]),
        FILTER(
            ALL('Calendar Table (Date)'),
            'Calendar Table (Date)'[Date] <= MAX('Calendar Table (Date)'[Date]) &&
            'Calendar Table (Date)'[Month] = MonthToUse
        )
    )

If a specific month is selected, the measure uses that month for the MTD calculation and if no month is selected (or all are deselected), it automatically defaults to the latest month.

[cj_oat]

Hi @Bibiano_Geraldo ,

it seems to work at high level, however, when I select some other filters (e.g. SKU, Product Group, Product Subgroup etc.), it seems the value is still showing at high level, is there any way to fix this?

Thank you so much!

[Bibiano_Geraldo]

Hi @cj_oat,

The issue you're facing occurs because the ALL('Calendar Table (Date)') function removes all filters, including those from other dimensions like SKU, Product Group, and Subgroup. To fix this, you need to use ALLSELECTED instead of ALL, as ALLSELECTED respects filters applied to other fields but ignores filters on the calendar table specifically.

Here's the updated formula:

__TY MTD (Net Sales) =
VAR SelectedMonth =
    MAX('Calendar Table (Date)'[Month]) -- Get the selected month, if any
VAR LatestMonth =
    MAXX(ALL('Calendar Table (Date)'), 'Calendar Table (Date)'[Month]) -- Get the latest month from the calendar table
VAR MonthToUse =
    IF(ISBLANK(SelectedMonth), LatestMonth, SelectedMonth) -- Use selected month or fallback to latest month
RETURN
    CALCULATE(
        SUM('Sales Report'[Net Sales]),
        FILTER(
            ALLSELECTED('Calendar Table (Date)'),
            'Calendar Table (Date)'[Date] <= MAX('Calendar Table (Date)'[Date]) &&
            'Calendar Table (Date)'[Month] = MonthToUse
        )
    )

[cj_oat]

Hi @Bibiano_Geraldo ,

Thanks for your patient and advices! I'm about to make it works but there is still one point that I cannot fix it...

there are some SKU that might not have data in all month as it might be sold-out e.g. SKU 1001, it has data until July 2024 (no data rows for this SKU exists in Sales Report after July 2024), so when I use the formula suggested and filter to see only this SKU (and not filter any specific month), it will show MTD in July instead while actually the latest month for that table is Dec 2024, so bascially I expect to see blank value since this SKU does not have any sales data in Dec 2024

[Bibiano_Geraldo · Accepted solution]

Hi @cj_oat,

__TY MTD (Net Sales) =
VAR SelectedMonth =
    MAX('Calendar Table (Date)'[Month]) -- Get the selected month, if any
VAR LatestMonthInContext =
    MAXX(
        FILTER(
            ALLSELECTED('Calendar Table (Date)'), -- Respect other filters but ensure no direct filters on the calendar table
            NOT ISBLANK(SUM('Sales Report'[Net Sales])) -- Check only months with sales data in the current filter context
        ),
        'Calendar Table (Date)'[Month]
    )
VAR MonthToUse =
    IF(ISBLANK(SelectedMonth), LatestMonthInContext, SelectedMonth) -- Use selected month or fallback to latest month in context
RETURN
    IF(
        ISBLANK(MonthToUse), 
        BLANK(), -- Return blank if no valid month exists for the current filter context
        CALCULATE(
            SUM('Sales Report'[Net Sales]),
            FILTER(
                ALLSELECTED('Calendar Table (Date)'), -- Keep all contextual filters except on the calendar table
                'Calendar Table (Date)'[Date] <= MAX('Calendar Table (Date)'[Date]) &&
                'Calendar Table (Date)'[Month] = MonthToUse
            )
        )
    )

[cj_oat]

Thanks @Bibiano_Geraldo ,that works for me!

[dharmendars007]

Hello @cj_oat , 

The issue you're encountering is likely related to the filter logic in your DAX formula. The measure seems to depend on a single selected month, which might not work well when multiple months or no months are selected.

__TY MTD (Net Sales) =
VAR SelectedMonth =
MAX('Calendar Table (Date)'[Month])
VAR LatestMonth =
CALCULATE(MAX('Calendar Table (Date)'[Month]),
ALL('Calendar Table (Date)'))
VAR FilteredMonth =
IF(
ISFILTERED('Calendar Table (Date)'[Month]),
SelectedMonth,
LatestMonth )
RETURN
CALCULATE(SUM('Sales Report'[Net Sales]),
FILTER(ALL('Calendar Table (Date)'),
'Calendar Table (Date)'[Month] = FilteredMonth
&& 'Calendar Table (Date)'[Date] <= MAX('Calendar Table (Date)'[Date])))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[anmolmalviya05]

Hi , Hope you are doing good!

Your formula doesn't account for a scenario where no month is selected, which is why you're seeing blank results when no specific month is chosen. You can adjust the DAX formula to dynamically determine the latest month when no month is selected, while still honoring any month filters.

Here’s the updated formula:

__TY MTD (Net Sales) =

VAR LatestDate = MAX('Calendar Table (Date)'[Date]) -- The latest date based on current filter context

VAR LatestMonth = CALCULATE(

MAX('Calendar Table (Date)'[Month]),

ALL('Calendar Table (Date)')

) -- Find the latest month across all data

RETURN

CALCULATE(

SUM('Sales Report'[Net Sales]),

FILTER(

ALL('Calendar Table (Date)'),

'Calendar Table (Date)'[Date] <= LatestDate &&

(

ISFILTERED('Calendar Table (Date)'[Month]) &&

'Calendar Table (Date)'[Month] = MAX('Calendar Table (Date)'[Month]) ||

NOT(ISFILTERED('Calendar Table (Date)'[Month])) &&

'Calendar Table (Date)'[Month] = LatestMonth

)

)

)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in

Subscribe my youtube channel for Microsoft Fabric and Power BI updates: https://www.youtube.com/@AnmolPowerBICorner

@cj_oat

[123abc]

Please try belwo Dax measure:

__TY MTD (Net Sales) =
VAR SelectedMonth = MAX('Calendar Table (Date)'[Month])
VAR LatestMonth = CALCULATE(MAX('Calendar Table (Date)'[Month]), ALL('Calendar Table (Date)'), 'Calendar Table (Date)'[Date] <= TODAY())
RETURN
CALCULATE(
SUM('Sales Report'[Net Sales]),
FILTER(
ALL('Calendar Table (Date)'),
'Calendar Table (Date)'[Date] <= MAX('Calendar Table (Date)'[Date]) &&
'Calendar Table (Date)'[Month] = IF(ISBLANK(SelectedMonth), LatestMonth, SelectedMonth)
)
)

i think the issue in your formula arises because the filter for the latest month does not function correctly when no month is selected. Here's how you can fix it:

• If a specific month is selected, the formula will calculate the MTD for the selected month.
• If no month is selected, it will calculate the MTD for the latest month.

Let me know if you face further issues!