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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mpeabody
Regular Visitor

Dax calculation for sales attained within fiscal period

Hello! I am needing a DAX formula that will calculate the percentage of goal attained for any given day within a period. For example, if we are 65% through the period and our goal for the period is 15M and we are currently at 10M in sales, how do I calculate the percentage of goal attained for that 65% of the period. At 10M in sales have we achieved 100% in relation to our overall goal of 15M being 65% through the period? 

Thank you!

1 ACCEPTED SOLUTION
Whitewater100
Solution Sage
Solution Sage

Hi:

Usually you will have a forecast or goal table.(phyiscal or virtual) It also depends on the grain of your forecast. Do you have an annual number that needs to be spread over each day, or perhaps a month goal and you would then spilt that up over the days in each month you are forecasting for.

Assumingyour sales comes in by day in a different table you can do a simple sum of sales and a sum of forecast and working in conjunction with a date table, the to date comparisons are not hard to do.

 

If you have a table with each month and a total budget for each month, as a separate table e.g.

Month   Forecast Amt

Jan             200

Feb            300

etc.

 

You can use this allocation DAX to break your monthly forecasts into daily amounts. Then you just sum then or do a YTD for both actual values vs. budgeted vales.

Here is a way to allocate a monthly budget to each day. *Note. You have a separate Dates table with all the dates, marked as a date table and connected to your fact table with sales in it. Dates[Date] (one side) is usually connected in a relationship to SalesTable[OrderDate] (many side) as one example.

Here's one allocation measure for spreading out the monthly budget. I hope this helps!

Budget Allocated =

SUMX (

    -- Summarize Dates by Year/Month & Days in Month

    SUMMARIZE (

        Dates,

        Dates[Year Month],

        Dates[Days In Month]

    ),

    -- For each Year/Month get # days in current filter context for that Year/Month

    VAR DayCount =

        CALCULATE ( COUNTROWS ( Dates ) )

    -- Budget value for the entire month

    VAR BudgetValueMonth =

        CALCULATE (

            SUM ( Budget[Budget Value] ),

            ALLEXCEPT ( Dates, Dates[Year Month] )

        )

    -- Budget value allocated to required number of days (DayCount)

    VAR BudgetValueAllocated =

        BudgetValueMonth * DayCount / Dates[Days In Month]

    RETURN

        BudgetValueAllocated

)

View solution in original post

2 REPLIES 2
Whitewater100
Solution Sage
Solution Sage

Hi:

To answer your question on % of goal.

1. Total Sales = SUM(SalesTable[Sales Amt])

2. Total Budget = SUM(Budget[Budget Amt])

3. % of Goal = DIVIDE([Total Sales], [Total Budget])  Foramt as %.

Whitewater100
Solution Sage
Solution Sage

Hi:

Usually you will have a forecast or goal table.(phyiscal or virtual) It also depends on the grain of your forecast. Do you have an annual number that needs to be spread over each day, or perhaps a month goal and you would then spilt that up over the days in each month you are forecasting for.

Assumingyour sales comes in by day in a different table you can do a simple sum of sales and a sum of forecast and working in conjunction with a date table, the to date comparisons are not hard to do.

 

If you have a table with each month and a total budget for each month, as a separate table e.g.

Month   Forecast Amt

Jan             200

Feb            300

etc.

 

You can use this allocation DAX to break your monthly forecasts into daily amounts. Then you just sum then or do a YTD for both actual values vs. budgeted vales.

Here is a way to allocate a monthly budget to each day. *Note. You have a separate Dates table with all the dates, marked as a date table and connected to your fact table with sales in it. Dates[Date] (one side) is usually connected in a relationship to SalesTable[OrderDate] (many side) as one example.

Here's one allocation measure for spreading out the monthly budget. I hope this helps!

Budget Allocated =

SUMX (

    -- Summarize Dates by Year/Month & Days in Month

    SUMMARIZE (

        Dates,

        Dates[Year Month],

        Dates[Days In Month]

    ),

    -- For each Year/Month get # days in current filter context for that Year/Month

    VAR DayCount =

        CALCULATE ( COUNTROWS ( Dates ) )

    -- Budget value for the entire month

    VAR BudgetValueMonth =

        CALCULATE (

            SUM ( Budget[Budget Value] ),

            ALLEXCEPT ( Dates, Dates[Year Month] )

        )

    -- Budget value allocated to required number of days (DayCount)

    VAR BudgetValueAllocated =

        BudgetValueMonth * DayCount / Dates[Days In Month]

    RETURN

        BudgetValueAllocated

)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors