cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gordo_c_123
Helper II
Helper II

Dynamic P&L fiscal year time intelligence for actual vs forecast vs budget vs same period last year

Hi Community,

 

I am trying to create a dynamic time intelligent profit and loss statement comparing actuals to forecast, budget, and the same period last year for each line item. I am using a fiscal year calendar which means the standard time intelligence measures will not work. The comparison amount (column) needs to be the variance between the two and not the amount. For example, net sales, the actual amount is 10,000 and the forecast amount is 12,000. The P&L matrix/table should show 10,000 for the actual amount and (2,000) for the forecast amount. Below is a quick picture of what I am trying to achieve:

 

Line-ItemActualvs Forecastvs Budgetvs SPLY
Net Sales10,000(2,000)(1,000)(3,000)
COGS4,0002,0001,000500

 

Additionally, I would like to see this on a WTD, MTD, QTD, and YTD basis in one matrix/table. Below is a screenshot of the final product I am trying to create along with the format strings:

gordo_c_123_5-1655314116615.png

As you can see, we have four different format strings: whole numbers, whole number dollars, decimal, and percentages.

 

My data model is pictured below:

gordo_c_123_2-1655313397641.png

 

Below is a picture of what I currently have:

gordo_c_123_6-1655314236937.png

I have created all the individual line-item measures for actuals, forecast, and budget using the measure branching technique. However, I know this is not the best way to do this because I have three measures for each line item. For example, I have a measure for actual net sales, forecast net sales, and budgeted net sales. As you can see, the forecast, budget, and SPLY measures are not variance amounts.

 

The actual, forecast and budget measures use the SWITCH technique which prevents me from calculating the variance because of the four different format strings and a custom time intelligence function. Below is an example of my Actuals measure:

 

Act HL =
SWITCH(
    [Selected Account],
    1, FORMAT([Volume], "#,#;(#,#);-"),
    2, FORMAT([Net Sales], "$#,#;($#,#);-"),
    3, FORMAT([COGS], "$#,#;($#,#);-"),
    4, FORMAT([Gross Margin],  "$#,#;($#,#);-"),
    5, FORMAT([MAP/MDF],  "$#,#;($#,#);-"),
    6, FORMAT([SG&A], "$#,#;($#,#);-"),
    7, FORMAT([Combined EBIT], "$#,#;($#,#);-"),
    9, FORMAT([Net Sales Rate], "$#,0.00;($#,0.00);-"),
    10, FORMAT([COGS Rate], "$#,0.00;($#,0.00);-"),
    11, FORMAT([Gross Margin Rate], "$#,0.00;($#,0.00);-"),
    12, FORMAT([MAP/MDF Rate], "$#,0.00;($#,0.00);-"),
    13, FORMAT([SG&A Rate], "$#,0.00;($#,0.00);-"),
    14, FORMAT([Combined EBIT Rate], "$#,0.00;($#,0.00);-"),
    16, FORMAT([GM as % of Net Sales], "0.0%;-0.0%;-"),
    17, FORMAT([Combined EBIT as % of Net Sales], "0.0%;-0.0%;-")
)

 

This measure above is then placed into a time intelligence pattern using the SQLBI DAX Patterns for Week-related calculations. As previously mentioned, I am using a fiscal year calendar so the standard time intelligence measures will not work. For example, the Act WTD column in my matrix above is:

 

Act WTD = 
IF ( 
    [ShowValueForDates],
    VAR LastDayOfWeekAvailable =  MAX ( 'Date'[Day of Week Number] )
    VAR LastFiscalYearWeekAvailable = MAX ( 'Date'[Fiscal Year Week Number] )
    VAR Result =
        CALCULATE (
            [Act HL],
            ALLEXCEPT ( 'Date', 'Date'[Working Day], 'Date'[Day of Week] ),
            'Date'[Day of Week Number] <= LastDayOfWeekAvailable,
            'Date'[Fiscal Year Week Number] = LastFiscalYearWeekAvailable
        )
    RETURN
        Result
)

 

 The matrix has 16 measures like you see above (actual + forecast + budget + SPLY = 4 x 4 = 16 measures)

 

Obviously this not an efficent or sustainable way to create this. I have a feeling Calculation Groups might help solve the problem. Additionally, when slicers are placed on the page, the query is slow because of the number of measures and the size of the data tables.

 

So community, how can I achieve the above? Thank you for taking the time to read through my question.

2 REPLIES 2
gordo_c_123
Helper II
Helper II

Hi @lbendlin 

 

Thank you for the response. Could you provide an example of what that would look like?

 

I'm only able to achieve the WTD, MTD, QTD, and YTD using one calculation group. I've never tried using more than one calculation group in a matrix before. 

lbendlin
Super User
Super User

yes, give calculation groups a try. They can also solve the formatting for you.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors