Forum Discussion

jimbob2285's avatar
jimbob2285
Advocate IV
1 year ago
Solved

Measure filter not working

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
rajendraongole1
Super User
1 year ago

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.