Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, is it possible to plot the below chart in power bi?
I want to compare the result for last year and this year in the same chart.
Solved! Go to Solution.
Hi @PBI_newuser ,
I created a sample pbix file(see attachment), please check whether that is what you want.
Best Regards
Hi @PBI_newuser ,
I created a sample pbix file(see attachment), please check whether that is what you want.
Best Regards
Hi @v-yiruan-msft , I have a question.
My data's fiscal year starts on October 01 and ends on September 30.
For example, current month is July. I would like to calculate all the measures from October to June only.
In FY2019 (Oct'18 to Sep'19) and FY2020 (Oct'19 to Sep'20), I have 12 months data but for FY2021 (Oct'19 to Jun'21), I have only 9 months data.
In the scatterplot, the Sales % and Revenue should calculate from Oct to Jun only for each fiscal year.
FY2019 = Oct'18 to Jun'19
FY2020 = Oct'19 to Jun'20
FY2021 = Oct'20 to Jun'21
May I know how calculate the measures up to last month for all the fiscal year? Thank you.
Hi @PBI_newuser ,
You can create measure as below(Assume that the table exist [Fisal Year] and [month number] field):
Measure =
VAR _maxdatemonth =
MONTH ( MAX ( 'Table'[Date] ) )
VAR _revenue =
CALCULATE (
SUM ( 'Table'[Revenue] ),
FILTER (
'Table',
'Table'[Fiscal Year] = SELECTEDVALUE ( 'Table'[Fiscal Year] )
&& IF (
_maxdatemonth >= 10,
'Table'[Month] >= 10
&& 'Table'[Month] <= _maxdatemonth,
( 'Table'[Month]
IN { 10, 11, 12 }
|| 'Table'[Month] >= 1
&& 'Table'[Month] <= _maxdatemonth )
)
)
)
RETURN
_revenue
Best Regards
Hi @v-yiruan-msft , the measure doesn't work. Here is the sample.
In the sample data, the max month is 2021-05.
I would like to view the result for each fiscal year from Oct to May. How to do it? Thanks.
Hi @PBI_newuser ,
I update the formula of measure as below and it can get your expected output just as shown in below screenshot. Please find the details in the attachment.
Measure =
VAR _maxdatemonth =
MONTH( CALCULATE( MAX ( 'Table'[Month] ),ALL('Table')) )
VAR _revenue =
CALCULATE (
[Ave Revenue/Order],
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] )
&& IF (
_maxdatemonth >= 10,
'Calendar'[Month number] >= 10
&& 'Calendar'[Month number] <= _maxdatemonth,
( 'Calendar'[Month number]
IN { 10, 11, 12 }
|| 'Calendar'[Month number] >= 1
&& 'Calendar'[Month number] <= _maxdatemonth )
)
)
)
RETURN
_revenue
|
Best Regards
HI @PBI_newuser
You can follow this link its very helpful for you
https://radacad.com/storytelling-with-power-bi-scatter-chart
Thank & Reagrds
Rohit Jaiswal
@PBI_newuser , Tried something like this.
Product and Year on Details. ( Drill till last level using reverse Y sign)
AUSP and Sales % on axis.
And conditional formating using scale on YOY measure