cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Custom User defined Slicer

Hi Team ,

 

I've a custom Slicer like below  

Slicer.PNG

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.

1 ACCEPTED SOLUTION

Hi @vasu538 ,

 

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.

View solution in original post

5 REPLIES 5
Microsoft
Microsoft

Hi @vasu538 ,

 

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 () ) )
)

9.png

 

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.

Hi @v-stephen-msft ,

 

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

 

vasu538_0-1605376115051.png

 

Thanks In Advance ,

Siddanth

Hi @vasu538 ,

 

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.

View solution in original post

Hi @v-stephen-msft ,

 

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

Super User IV
Super User IV

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors