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 the same for the respective selected value in the slicer.

I need to developa custom sclicer and the same slicer sync with the chart. attached in the Pbix File.

Here is the Link : https://1drv.ms/u/s!Au-aOkl1BoHugiyEUg_NcRen5bgw?e=dmCFHi

Can you please help me out on these ?

Thanks In Advance

Siddanth.

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.

5 Replies

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.

mahoneypat
Microsoft Employee
5 years ago
I looked at your pbix. You'll need to add a Date table to your model, and then use a SWITCH function to get your desired functionality, using the SELECTEDVALUE of your slicer to determine with Time Intelligence function to use in your measure.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Regards,

Pat

Forum Discussion

Custom User defined Slicer

5 Replies

Related content

User defined Custom Periods Slicer

un define

Custom Date Slicers

slicer custom order

Custom Date Range Slicer