Forum Discussion

raphazzz's avatar
raphazzz
Icon for Helper I rankHelper I
3 years ago

Help with a matrix table that is adding values to all months instead to only specific months.

Hello Folks,

I have a matrix which has months as Rows, and I have 4 columns with values.

Column 1 has the sales projections (quantity).
Column 2 has actual sales (quantity)
Column 3 has actual sales (Season End Estimates) for past months, estimate sales for the current month and projected sales for future months.
Column 4 it just a % variance between Column 1 (Projection) and column 3 (Season End Estimates)

My problem is the calculation with the column 3.

The measure is adding the current month estimate (which is basically the actual sales for the month transformed divide by days elapsed in the current month, multiplied by the total days of the month) to all months.


The column 3 should only have actuals for Apr, May and Jun from Column 2, for Jul is correct and Ago and Sept the amonts from column 1. The measure is adding 40,133 to all months.

That's the measure:

z3 Est. Act. + Est. + Proj. Accounts = 
VAR A =
    //Used to calculate accounts sold in the previous months (For the Actuals).
    CALCULATE (
        COUNTROWS ( 'SalesReport' ),
        FILTER (
            SalesReport,
            SalesReport[Status] <> "Canceled"
                && SalesReport[Active] = "YES"
        ),
        SalesReport[Date Sold]
            <= EOMONTH (
                TODAY (),
                -1
            )
    )

VAR B = 
    // Used to calculate the current month sales, see the second measure
    [zCM Est. Total Sales by Subs]

VAR C =
    // Used to calculate the projected new accounts for the future months
    CALCULATE (
        'zEstimate Formulas'[z1 Est. Proj. Accounts],
        'Sales Projection 2023'[projection_month]
            > TODAY ()
    )
    
RETURN
A + B + C


This the reference measure for var B

zActual CM Service Count = 
CALCULATE (
    COUNTROWS ( 'SalesReport' ),
    FILTER (
        SalesReport,
        SalesReport[Status] <> "Canceled"
            && SalesReport[Active] = "YES"
            && MONTH(SalesReport[Date Sold]) = MONTH(TODAY())
    )
)


If I return only VAR A or C or A + C, it brings the correct values.

 

Please help! Thanks


Need Help

3 Replies

  • flath's avatar
    flath
    Icon for Helper II rankHelper II
    3 years ago

    Hi!

    Please, try the following codes.

     

    z3 Est. Act. + Est. + Proj. Accounts = 
VAR StartDate = MIN( SalesReport[Date Sold] )
var EndDate = LASTDATE( SalesReport[Date Sold] )

VAR A =
    //Used to calculate accounts sold in the previous months (For the Actuals).
    CALCULATE (
        COUNTROWS ( 'SalesReport' ),
        SalesReport[Status] <> "Canceled" && SalesReport[Active] = "YES",
        DATESBETWEEN( SalesReport[Date Sold], StarDate, EndDate)
        )

VAR B = 
    // Used to calculate the current month sales, see the second measure
    [zCM Est. Total Sales by Subs]

VAR C =
    // Used to calculate the projected new accounts for the future months
    CALCULATE (
        'zEstimate Formulas'[z1 Est. Proj. Accounts],
        'Sales Projection 2023'[projection_month]
            > TODAY ()
    )
    
RETURN
A + B + C


    This the reference measure for var B

    zActual CM Service Count =    

CALCULATE (
    COUNTROWS ( 'SalesReport' ),
    SalesReport[Status] <> "Canceled" && SalesReport[Active] = "YES",
    DATESMTD(SalesReport[Date Sold )
)

     

    • raphazzz's avatar
      raphazzz
      Icon for Helper I rankHelper I
      3 years ago

      I tried your suggestion but it does not work (it's not returning the summing from the right column). It's returning the SUM of actual and not the column estimate actual (which is the actual sales convertend to mont-end amount).


      This table has a relationship between a date table and a table were I summarized the data based on location.


      It is not calculating if I keep the relationship active (because I didn't have sales today), but the formula should look into the whole month and not only on the 27. If I create an additional column with only month and year and change the relationship from date to date to current month/year to current month/year, it creates a circular reference (many-to-many relationship)

      If I simple delete the relationship, it adds the sum of estimate in all months (just like my original post).