Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
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.
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
Thanks In Advance ,
Siddanth
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.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |