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

Last12 months based on month, year, quarter slicer

Hello Team,

Can anyone help me with below issue
my requirement is
Table A I have sales, and date column( for 2019 and 2020 but in future it will increase)
I am using clustured column chart to display data
we are using 4 filters. Quartar slicer, month slicer, year slicer based on date column and country
By default chart should show last 12 months.
when we select anything in month slicer it should show from that month to last 12 months
example: if i select november in slicer it should show from 2020 december to 2021 november.
when I select quartar in slicer it should only that quartar
example: i selected "3" means july,august,september only that data should come
if i select year it should show only that year months. 
Could you please help to solve this issue.
I tried by doing disconnected date table and used below dax it is working for 12 months but for slicers it is not effecting properly. 
12months =
Var SelectedDate = MAX('Date table'[Date])
var PreviousDate = Date(year(SelectedDate),month(SelectedDate)-12,day(SelectedDate))
var result =
calculate(
sum(Table[sales]),filter(Table,Table[date] >= PreviousDate && Table[date] <= SelectedDate))
return result


please help me

Thanks

 

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

Hi @Balhemkan ,

 

You need to create three date table for each slicer(my sample data):

 

Dim_date = CALENDAR(DATE(2013,1,1),DATE(2015,12,31))

Dim_Datefor qtr = ADDCOLUMNS(Dim_Date,"qtr",QUARTER(Dim_date[Date])) 

Dim_Datefor year = ADDCOLUMNS(Dim_Date,"Year",YEAR(Dim_Date[Date])) 

 

Then use switch to calculate your measure:

 

SalesAmount =
SWITCH (
    TRUE (),
    HASONEFILTER ( 'Dim_Datefor qtr'[qtr] ),
        CALCULATE (
            SUM ( Sales[Sales] ),
            FILTER (
                Sales,
                QUARTER ( Sales[OrderDate] ) = SELECTEDVALUE ( 'Dim_Datefor qtr'[qtr], 1 )
            )
        ),
    HASONEFILTER ( 'Dim_Datefor year'[Year] ),
        CALCULATE (
            SUM ( Sales[Sales] ),
            FILTER (
                Sales,
                YEAR ( Sales[OrderDate] ) = SELECTEDVALUE ( 'Dim_Datefor year'[Year] )
            )
        ),
    VAR a =
        MAX ( Dim_date[Date] )
    VAR b =
        DATE ( YEAR ( a ), MONTH ( a ) - 12, DAY ( a ) )
    RETURN
        CALCULATE (
            SUM ( Sales[Sales] ),
            FILTER ( Sales, Sales[OrderDate] >= b && Sales[OrderDate] <= a )
        )
)

 

1.gif

 

For more details, please refer to pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ebkf_PgMiSZKlBBAR-...

 

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

 

Best Regards,

Dedmon Dai

 

View solution in original post

6 REPLIES 6
v-deddai1-msft
Community Support
Community Support

Hi @Balhemkan ,

 

You need to create three date table for each slicer(my sample data):

 

Dim_date = CALENDAR(DATE(2013,1,1),DATE(2015,12,31))

Dim_Datefor qtr = ADDCOLUMNS(Dim_Date,"qtr",QUARTER(Dim_date[Date])) 

Dim_Datefor year = ADDCOLUMNS(Dim_Date,"Year",YEAR(Dim_Date[Date])) 

 

Then use switch to calculate your measure:

 

SalesAmount =
SWITCH (
    TRUE (),
    HASONEFILTER ( 'Dim_Datefor qtr'[qtr] ),
        CALCULATE (
            SUM ( Sales[Sales] ),
            FILTER (
                Sales,
                QUARTER ( Sales[OrderDate] ) = SELECTEDVALUE ( 'Dim_Datefor qtr'[qtr], 1 )
            )
        ),
    HASONEFILTER ( 'Dim_Datefor year'[Year] ),
        CALCULATE (
            SUM ( Sales[Sales] ),
            FILTER (
                Sales,
                YEAR ( Sales[OrderDate] ) = SELECTEDVALUE ( 'Dim_Datefor year'[Year] )
            )
        ),
    VAR a =
        MAX ( Dim_date[Date] )
    VAR b =
        DATE ( YEAR ( a ), MONTH ( a ) - 12, DAY ( a ) )
    RETURN
        CALCULATE (
            SUM ( Sales[Sales] ),
            FILTER ( Sales, Sales[OrderDate] >= b && Sales[OrderDate] <= a )
        )
)

 

1.gif

 

For more details, please refer to pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ebkf_PgMiSZKlBBAR-...

 

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

 

Best Regards,

Dedmon Dai

 

View solution in original post

Hi @v-deddai1-msft  Excellent. Thank you so much for help

amitchandak
Super User IV
Super User IV

@Balhemkan , Try with a minor change

 

12months =
Var SelectedDate = MAXX(AllSelected('Date table'),'Date table'[Date])
var PreviousDate = Date(year(SelectedDate),month(SelectedDate)-12,day(SelectedDate))
var result =
calculate(
sum(Table[sales]),filter(Table,Table[date] >= PreviousDate && Table[date] <= SelectedDate))
return
result

 

or refer to this video : https://www.youtube.com/watch?v=duMSovyosXE



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak , filter is working for month but still if I select quarter it's not effecting chat still. 

Could you please help me which table column I need to take in slicer. 

I mean I need to take date table slicer or table slicer

@amitchandak 

Hi amit, 

I can see last 12 months based on month slicer. 

I can see year wise months if I select year slicer

Same way I need to see quarter if I select qtr1 in quarter slicer

Example in quarter slicer we have values like Q1, Q2, Q3, Q4

If I select Q3 in quarter slicer I need to see In chart only July, August , September. 

Please help me

@Balhemkan , do you want to change the axis/row also when you change it to qtr? select Qtr then show 4 qtr, select month show 12 months

 

Then bookmark and button or solution like these are needed

Dynamically change chart axis in Power BI
https://www.youtube.com/watch?v=6jeSIRpjv0M
https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors