Measure filter not working

[jimbob2285]

Hi

I need some help, please...

I've created the following measure filter, which allows the user to select a month and filter a table to show only the months from the beginning of the year up to the month selected, as they have a need to look at the monthly transaction values only up to a certain period

_SelectedMonth_Cumulative = 
VAR MyFilterTable = SUMMARIZE(FILTER(ALL('Calendar'), 'Calendar'[Date] <= SELECTEDVALUE('Calendar'[Date])),'Calendar'[Date])
RETURN
IF(MAX(Ledger[Date]) IN MyFilterTable, 1, 0)

The Ledger-Transactions file is a much simplified version of the full data set, but the principles are the same

If you open the link to the Ledger-Transactions PBIX file, you'll see that I've created two other measure filters in the same manner, to filter to:

• Cummulative transactions up to the month previous to the selected month
• transactions just for the month selected

and while the the measure filter in the code example above is working in the monthly table, It and the other two are not working in the three cards on the left of the page, I can't access the dropdowns to set the filters, it's as if they're invaid

Now, I understand why this is, it's a matter of context, which i take to mean that measures only work on the visuals and are not a column in the table, so if the visual doesn't contain the relevant column(s), then the measure won't work as a filter... which makes sense to me

And you'll see that for the purposes of demonstrating how I'd like these three cards to appear, I've filtered them with a column filter instead, where the selected date is hard coded into the DAX for the columns

But i need to filter them with a meausre, as it needs to be reactive to the month slicer, which a column is not, so the question is, can this be acheived and if so, how?

Thanks in advance for any help and advice you can offer

Cheers

Jim

[Anonymous · Accepted solution]

Hi @jimbob2285 

Please try this:

Here I create 3 measures:

Selected Month - Month =
VAR _Slicer =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Ledger[Revenue] ),
        FILTER (
            ALLSELECTED ( Ledger ),
            YEAR ( 'Ledger'[Date] ) = YEAR ( _Slicer )
                && MONTH ( 'Ledger'[Date] ) = MONTH ( _Slicer )
        )
    )

Selected Month - Cumulative =
VAR _Slicer =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Ledger[Revenue] ),
        FILTER (
            ALLSELECTED ( Ledger ),
            YEAR ( 'Ledger'[Date] ) = YEAR ( _Slicer )
                && 'Ledger'[Date] <= _Slicer
        )
    )

Previous Month - Cumulative =
VAR _Slicer =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Ledger[Revenue] ),
        FILTER (
            ALLSELECTED ( Ledger ),
            YEAR ( 'Ledger'[Date] ) = YEAR ( _Slicer )
                && 'Ledger'[Date] < _Slicer
        )
    )

Then add the 3 measures in the three cards, the result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @jimbob2285 -calculates the sum of transactions from the start of the year up to the selected month dynamically

Cumulative_UpTo_SelectedMonth =
VAR MaxDate = MAX('Calendar'[Date]) -- Max date in the current visual context
RETURN
CALCULATE(
SUM(Ledger[TransactionValue]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] <= MaxDate
)
)

calculates the sum of transactions up to the month previously month-1 , calculate it by taking the reference of above logic .

similarly, for we can calculates the sum of transactions for only the selected month.

SelectedMonth_Transactions =
VAR MaxDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
SUM(Ledger[TransactionValue]),
FILTER(
ALL('Calendar'),
YEAR('Calendar'[Date]) = YEAR(MaxDate) &&
MONTH('Calendar'[Date]) = MONTH(MaxDate)
)
)

Add a Month slicer from the Calendar table to your report page. I hope this works.