Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Balhemkan
Helper III
Helper III

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

 

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

amitchandak
Super User
Super User

@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

@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/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.