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.
Hi all,
I wanted to create something using power BI which I have 2 sets of data comprise ACT and BUD within the same year.
Where I have my actual revenue for 3 months and 12 months of budget. But when I select my slicer accordingly to my months I need a projection something like below shown.
As I only have 3 months of actual, I need to compare 6 months of budget using 3 months actual and 3 month budget depend on the slicer chosen by the user (whether is up to 12 month or lesser).
The structure of my data look like this: **I will unpivot ACT and BUD
Ops Unit | ID | MTH | MTH ORDER | ACT | BUD |
A | REVENUE | JUL | 1 | 10 | 20 |
A | REVENUE | AUG | 2 | 10 | 20 |
A | REVENUE | SEP | 3 | 10 | 20 |
A | REVENUE | OCT | 4 | 20 | |
A | REVENUE | NOV | 5 | 20 | |
A | REVENUE | DEC | 6 | 20 | |
A | REVENUE | JAN | 7 | 20 | |
A | REVENUE | FEB | 8 | 20 | |
A | REVENUE | MAR | 9 | 20 | |
A | REVENUE | APR | 10 | 20 | |
A | REVENUE | MAY | 11 | 20 | |
A | REVENUE | JUN | 12 | 20 | |
B | REVENUE | JUL | 1 | 100 | 50 |
B | REVENUE | AUG | 2 | 100 | 50 |
B | REVENUE | SEP | 3 | 100 | 50 |
B | REVENUE | OCT | 4 | 50 | |
B | REVENUE | NOV | 5 | 50 | |
B | REVENUE | DEC | 6 | 50 | |
B | REVENUE | JAN | 7 | 50 | |
B | REVENUE | FEB | 8 | 50 | |
B | REVENUE | MAR | 9 | 50 | |
B | REVENUE | APR | 10 | 50 | |
B | REVENUE | MAY | 11 | 50 | |
B | REVENUE | JUN | 12 | 50 |
Eg: When I select Dec then it give me something like this
Ops Unit | FCT (3month actual + 3 month budget) | BUD |
A | 90 | 120 |
B | 450 | 300 |
I tried a few method but it seem unable to create what I wanted and also search within forum too.
Is there any way I can do? Please help. Thank you.
Solved! Go to Solution.
Hi @Anonymous ,
If your table doesn’t have a date column, just have a month column, we can create a MTH table and two measures to meet your requirement.
Please pay attention, don’t create a relationship.
1. Create a table that contains MTH and MTH order.
slicer = SUMMARIZE('Table','Table'[MTH],'Table'[MTH ORDER])
2. Then we can use it to create a slicer.
3. We can create two measures.
BUD measure =
VAR _Select =
SELECTEDVALUE ( slicer[MTH ORDER], 1 )
RETURN
CALCULATE (
SUM ( 'Table'[BUD] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Ops Unit] = MAX ( 'Table'[Ops Unit] )
&& 'Table'[MTH ORDER] <= _Select
)
)
ACT measure =
VAR _Select =
SELECTEDVALUE ( slicer[MTH ORDER], 1 )
VAR _ACT =
CALCULATE (
SUM ( 'Table'[ACT] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Ops Unit] = MAX ( 'Table'[Ops Unit] )
&& 'Table'[MTH ORDER] <= _Select
)
)
VAR _max_act_month =
CALCULATE (
MAX ( 'Table'[MTH ORDER] ),
FILTER ( 'Table', 'Table'[ACT] = MAX ( 'Table'[ACT] ) )
)
VAR _BUD_max_act_month =
CALCULATE (
SUM ( 'Table'[BUD] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Ops Unit] = MAX ( 'Table'[Ops Unit] )
&& 'Table'[MTH ORDER] <= _max_act_month
)
)
VAR _BUD =
CALCULATE (
SUM ( 'Table'[BUD] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Ops Unit] = MAX ( 'Table'[Ops Unit] )
&& 'Table'[MTH ORDER] <= _Select
)
)
RETURN
IF ( _Select > _max_act_month, _BUD_max_act_month + _ACT, _ACT + _BUD )
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @Anonymous ,
If your table doesn’t have a date column, just have a month column, we can create a MTH table and two measures to meet your requirement.
Please pay attention, don’t create a relationship.
1. Create a table that contains MTH and MTH order.
slicer = SUMMARIZE('Table','Table'[MTH],'Table'[MTH ORDER])
2. Then we can use it to create a slicer.
3. We can create two measures.
BUD measure =
VAR _Select =
SELECTEDVALUE ( slicer[MTH ORDER], 1 )
RETURN
CALCULATE (
SUM ( 'Table'[BUD] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Ops Unit] = MAX ( 'Table'[Ops Unit] )
&& 'Table'[MTH ORDER] <= _Select
)
)
ACT measure =
VAR _Select =
SELECTEDVALUE ( slicer[MTH ORDER], 1 )
VAR _ACT =
CALCULATE (
SUM ( 'Table'[ACT] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Ops Unit] = MAX ( 'Table'[Ops Unit] )
&& 'Table'[MTH ORDER] <= _Select
)
)
VAR _max_act_month =
CALCULATE (
MAX ( 'Table'[MTH ORDER] ),
FILTER ( 'Table', 'Table'[ACT] = MAX ( 'Table'[ACT] ) )
)
VAR _BUD_max_act_month =
CALCULATE (
SUM ( 'Table'[BUD] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Ops Unit] = MAX ( 'Table'[Ops Unit] )
&& 'Table'[MTH ORDER] <= _max_act_month
)
)
VAR _BUD =
CALCULATE (
SUM ( 'Table'[BUD] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Ops Unit] = MAX ( 'Table'[Ops Unit] )
&& 'Table'[MTH ORDER] <= _Select
)
)
RETURN
IF ( _Select > _max_act_month, _BUD_max_act_month + _ACT, _ACT + _BUD )
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi Zhenbw,
WOW. It seems like what I needed for my dashboard.
Let me try and will get back to you see asap as I just return back to work.
Hello, @v-zhenbw-msft
Sorry can trouble you to share you pbix file as I it written it incompatible with my current version of Power BI desktop.
Even after redownload it, it seems that still appear this issue.
Please advise? Thanks.
Hi @Anonymous ,
Please try the Power BI Store version.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , In case you do not have date
create one like
date = Date(Table[Year], table[MTH], 1) //Numeric month
then use date table and time intelligence
YTD Act= CALCULATE(SUM(Table[ACT]),DATESYTD('Date'[Date],"12/31"))
YTD Bud= CALCULATE(SUM(Table[BUD]),DATESYTD('Date'[Date],"12/31"))
Power BI — YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
In case you do not have month as a number - refer first few mins of this video
https://www.youtube.com/watch?v=yPQ9UV37LOU&t=30s
Hi Amit,
Thanks for your YTD DAX formula but it does not solved my issue having both actual and budget together.
Anyway thanks for your help.
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 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |