Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Custom User defined Slicer

Hi Team ,   I've a custom Slicer like below   And having column chart with months and amount by month. for ex : When i select the last quarter it should display only last quarter months  and...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Try this measure.

    Measure = 
VAR EndDate =
    EOMONTH ( TODAY (), 0 )
VAR StartDate =
    EDATE ( EOMONTH ( TODAY (), -1 ), -11 ) + 1
RETURN
SWITCH (
    SELECTEDVALUE ( DCal[Types] ),
    "Last Quarter",
        CALCULATE (
            SUM ( Sheet1[Amount] ),
            FILTER (
                'Sheet1',
                QUARTER ( [Date] )
                    = QUARTER ( TODAY () ) - 1
                    && YEAR ( [Date] ) = YEAR ( TODAY () )
            )
        ),
    "Last Month",
        CALCULATE (
            SUM ( 'Sheet1'[Amount] ),
            FILTER (
                'Sheet1',
                MONTH ( [Date] )
                    = MONTH ( TODAY () ) - 1
                    && YEAR ( [Date] ) = YEAR ( TODAY () )
            )
        ),
    "This Month",
        CALCULATE (
            SUM ( 'Sheet1'[Amount] ),
            FILTER (
                'Sheet1',
                MONTH ( [Date] ) = MONTH ( TODAY () )
                    && YEAR ( [Date] ) = YEAR ( TODAY () )
            )
        ),
    "This Quarter",
        CALCULATE (
            SUM ( Sheet1[Amount] ),
            FILTER (
                'Sheet1',
                QUARTER ( [Date] ) = QUARTER ( TODAY () )
                    && YEAR ( [Date] ) = YEAR ( TODAY () )
            )
        ),
    "This Week",
        CALCULATE (
            SUM ( Sheet1[Amount] ),
            FILTER (
                'Sheet1',
                WEEKNUM ( [Date] ) = WEEKNUM ( TODAY () )
                    && YEAR ( [Date] ) = YEAR ( TODAY () )
            )
        ),
    "This Year",
        CALCULATE (
            SUM ( Sheet1[Amount] ),
            FILTER ( 'Sheet1', [Date]<=EndDate&&[Date]>=StartDate)
        ),
    "YTD", CALCULATE ( SUM ( 'Sheet1'[Amount] ), FILTER ( 'Sheet1', [Date] <= TODAY () ) )
)

     

    Please note that there is no data for December 2019 in your sample data.

     

    Reference: https://www.ehansalytics.com/blog/2019/3/3/calculate-last-twelve-months-using-dax

     

     

     

    Best Regards,

    Stephen Tao

     

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