Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi everyone. I hav tried to crack this one but have hit a wall.
I have a report with a date table with date/ fiscal period/ fiscal weeks etc. I also have a budget table where each period has a budget (P1,P2,P3 etc).
I want to be able to create a measure where the Period Budget is divided by the number of fiscal weeks in the period to give a Fiscal week budget. I need this to act so I have have a visual table with Period/Fiscal Week/ Revenue/Budget.
I hae been able to create measures to work out the (Max fiscal week - Min fiscal week) +1 to work ou the number of weeks, but when I filer te visual by period or fiscal week it breaks.
I have atached a sample file to show the tables and relationship.
Thank you in advance
Solved! Go to Solution.
Hi @Anonymous ,
According to your description, I create a sample.
dim_dates table:
dim_target table:
The two tables are related with Fiscal Period column and have a both direction.
Then create a measure.
Fiscal week budget =
VAR _Total =
MAX ( 'dim_targets'[Target] )
VAR _MAX =
MAXX (
FILTER (
ALL ( dim_dates ),
'dim_dates'[Fiscal Period] = MAX ( 'dim_targets'[Fiscal Period] )
),
'dim_dates'[Fiscal Week]
)
VAR _MIN =
MINX (
FILTER (
ALL ( dim_dates ),
'dim_dates'[Fiscal Period] = MAX ( 'dim_targets'[Fiscal Period] )
),
'dim_dates'[Fiscal Week]
)
RETURN
DIVIDE ( _Total, _MAX - _MIN + 1 )
Get the correct result.
The Fiscal Period and Fiscal Week slicer can only filter the rows.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
According to your description, I create a sample.
dim_dates table:
dim_target table:
The two tables are related with Fiscal Period column and have a both direction.
Then create a measure.
Fiscal week budget =
VAR _Total =
MAX ( 'dim_targets'[Target] )
VAR _MAX =
MAXX (
FILTER (
ALL ( dim_dates ),
'dim_dates'[Fiscal Period] = MAX ( 'dim_targets'[Fiscal Period] )
),
'dim_dates'[Fiscal Week]
)
VAR _MIN =
MINX (
FILTER (
ALL ( dim_dates ),
'dim_dates'[Fiscal Period] = MAX ( 'dim_targets'[Fiscal Period] )
),
'dim_dates'[Fiscal Week]
)
RETURN
DIVIDE ( _Total, _MAX - _MIN + 1 )
Get the correct result.
The Fiscal Period and Fiscal Week slicer can only filter the rows.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Amazing @amitchandak thank you so much. I was actually really close with my original solution but neglected the MINX and MAXX.
Than you so much for the help and such a detailed answer!
Thank you @amitchandak I appreciate the help. I can't seem to get it to generate the result though.
consistantly get the error The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
I am unsure where the extra column reference is?
@Anonymous , a new table
Var _tab=
filter(
generate(selectcolumns(Target,"FY",[FY Period], "Target", [Target]) , summarize(Date, date[FY Period],[FY Week])), [[FY Period] =[FY])
return
addcolumns(_tab , "New Target", divide([Target], countx(filter(_tab, [FY] = earlier([FY])), [FY Week])))
You also try to approch like
User | Count |
---|---|
136 | |
70 | |
69 | |
54 | |
53 |
User | Count |
---|---|
207 | |
94 | |
64 | |
61 | |
57 |