Forum Discussion

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

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.

Anonymous

Not applicable

5 years ago

Hi Anonymous ,

Put the measre into Values of the chart.

Measure = 
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', YEAR ( [Date] ) = YEAR ( TODAY () ) )
        ),
    "YTD", CALCULATE ( SUM ( 'Sheet1'[Amount] ), FILTER ( 'Sheet1', [Date] <= TODAY () ) )
)

You can check more details from here.

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.

Anonymous

Not applicable

5 years ago

Hi Anonymous ,

here is the attached link: https://1drv.ms/u/s!Au-aOkl1BoHugkbkXD_Rvqmf4DK8?e=1Ev9BF

Its working fine . I need one more requirement like when we select current year (i.e,2020) then we need to display 12 months from currentmonth i.e. dec 2019,jan 2020,feb 2020,march 2020,.... nov 2020(current Month). graph always should display 12 months of data.

and atttached snap is expected output.

Expected output

Thanks In Advance ,

Siddanth

Anonymous

Not applicable

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.

Anonymous
Not applicable
5 years ago
Hi Anonymous ,

I've tried the same thing but it didn't work me (Previous Year measure). Attached is the pbix file

Here is the link : https://1drv.ms/u/s!Au-aOkl1BoHuglWJsqvs_GgNx7vp?e=kHzgTu

Can you please hekp me out on these?

Thanks In Advance
Sid

Forum Discussion

Custom User defined Slicer

Related content

User defined Custom Periods Slicer

un define

Custom Date Slicers

slicer custom order

Custom Date Range Slicer