Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 )
)
)
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 @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 )
)
)
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
@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
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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |