Results 1 to 2 of 2

Thread: DAX Subtotals Profit and Loss statement
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,376
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default DAX Subtotals Profit and Loss statement

    I'm using the following method from Imke to produce a nicely formatted pivot table in Excel: https://www.thebiccountant.com/2016/...wer-pivot-dax/

    However, I am building on this to produce projected numbers.

    For each reporting heading the user can choose to use a YTD average for the remaining periods or the latest month for each of the remaining periods.

    The first measure is the same as Imke's post:
    Amt_ :=
    CALCULATE ( [Amt], AccountsAllocation )



    The next measure uses the selected projection method to work out how much is to be applied to each future period:
    BasisMonth :=
    IF (
    MIN ( ReportsAccountsLayout[Level] ) > 0,
    BLANK (),

    IF (
    [pBasis] = "YTD Average",
    [Amt_]
    / (
    MAX ( CurrentPeriod[CurrentPeriod] )
    - TRUNC ( MAX ( CurrentPeriod[CurrentPeriod] ) / 100 ) * 100
    ),
    CALCULATE (
    [Amt_],
    FactActuals[Period] = VALUES ( CurrentPeriod[CurrentPeriod] )
    )
    )
    )



    For October CurrentPeriod[CurrentPeriod] would be 202007. The financial year starts in April.

    And finally, the basismonth is multiplied by the number of remaining periods:
    ProjectedActual :=
    [BasisMonth]
    * (
    12
    - (
    MAX ( CurrentPeriod[CurrentPeriod] )
    - TRUNC ( MAX ( CurrentPeriod[CurrentPeriod] ) / 100 ) * 100
    )
    )



    At the moment subtotals are returning blanks (Bold in BasisMonth measure above) because I have no idea how to return the correct result.

    Is anyone able to help?
    Last edited by Comfy; Oct 14th, 2019 at 10:07 AM.

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,376
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DAX Subtotals Profit and Loss statement

    I went back to the drawing board and had a little think and I think I'm making it more complicated than I need to.

    I'm currently trying to apply different formulas in the same measure and am trying to ask the subtotal to deal with this.

    Instead what I think I should do is create three measures.

    YTDMeasure - that calculates the YTD average or returns null if the Basis is montly
    MonthlyMeasure - that calculates the current month or returns null if the basis is YTD
    BasisMeasure - that sums the two above.

    Which hopefully means the two different projection methods would propagate through to the subtotal..?!?!??11

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •