Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Divide a fiscal period target by the number of fiscal weeks in the period (With Date Table)

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.

DataDownUnder_0-1661839578380.png

 

DataDownUnder_1-1661839607936.png

 

Thank you in advance

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

According to your description, I create a sample.

dim_dates table:

vkalyjmsft_0-1662087126329.png

dim_target table:

vkalyjmsft_1-1662087159993.png

The two tables are related with Fiscal Period column and have a both direction.

vkalyjmsft_2-1662087196259.png

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.

vkalyjmsft_3-1662087424410.png

The Fiscal Period and Fiscal Week slicer can only filter the rows.

vkalyjmsft_4-1662087654292.png

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.

View solution in original post

4 REPLIES 4
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

According to your description, I create a sample.

dim_dates table:

vkalyjmsft_0-1662087126329.png

dim_target table:

vkalyjmsft_1-1662087159993.png

The two tables are related with Fiscal Period column and have a both direction.

vkalyjmsft_2-1662087196259.png

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.

vkalyjmsft_3-1662087424410.png

The Fiscal Period and Fiscal Week slicer can only filter the rows.

vkalyjmsft_4-1662087654292.png

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.

Anonymous
Not applicable

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!

Anonymous
Not applicable

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?

 

DataDownUnder_0-1662009277843.png

 

amitchandak
Super User
Super User

@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

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.