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 actua...
Need Help
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).