Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello alll,
I'm showing current month against some dimension and i want other month to be target for current month comparison(it's random)
for example if i'm showing July-2021 sales and i want Dec-2020 (it has to be dynamic..it can be May 2021)as the target.How to do this in Power BI Desktop??
From the above scenario,2016-July is cur sales and target to be 2015 -June
Solved! Go to Solution.
Hi @likhithar
Build an unrelated calendar table for target slicer and build a measure to show tagret value.
Unrelated calendar table
Target Slicer Table = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"))
Target measure:
Target =
VAR _SELECTYEAR =
SELECTEDVALUE ( 'Target Slicer Table'[Year] )
VAR _SELECTMONTH =
SELECTEDVALUE ( 'Target Slicer Table'[MonthName] )
RETURN
CALCULATE (
SUM ( Sheet2[Value] ),
FILTER (
ALL ( Sheet2 ),
Sheet2[Category] = MAX ( Sheet2[Category] )
&& Sheet2[Year] = _SELECTYEAR
&& Sheet2[MonthName] = _SELECTMONTH
)
)
Then we build a visual as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @likhithar
Build an unrelated calendar table for target slicer and build a measure to show tagret value.
Unrelated calendar table
Target Slicer Table = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"))
Target measure:
Target =
VAR _SELECTYEAR =
SELECTEDVALUE ( 'Target Slicer Table'[Year] )
VAR _SELECTMONTH =
SELECTEDVALUE ( 'Target Slicer Table'[MonthName] )
RETURN
CALCULATE (
SUM ( Sheet2[Value] ),
FILTER (
ALL ( Sheet2 ),
Sheet2[Category] = MAX ( Sheet2[Category] )
&& Sheet2[Year] = _SELECTYEAR
&& Sheet2[MonthName] = _SELECTMONTH
)
)
Then we build a visual as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @likhithar https://youtu.be/cyOquvfhzNM ...please refer this. I hope this will resolve your query.
Let me know whether it is working in your case or not.
@NidhiBhusari Attached an image in the question for reference.You can try with northwindmill dataset also
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
98 | |
97 | |
73 | |
72 |