Calculate Matrix Total by Customer, Product and Calendar Dimensions

[etane]

Hello.

Please help construct a measure that does the following:
1. Calculate total and subtotal by rep/customer, product and month

2. Forecast has to be last modefied on or before the 15th during the same month as CaseDate1

     
This is my work in progress:

However, the total should be as below:

Link to working file is here: Link

Thanks!

[v-sathmakuri · Accepted solution]

Hi @etane ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @rohit1991  for your response.

Below calculated column will return your expected results. Also attached the pbix file for your reference.

Forecast_Selected_Column =

VAR _CurrentMonthStart =

    DATE(

        YEAR(RELATED(Calendar[Date])),

        MONTH(RELATED(Calendar[Date])),

        1

    )

VAR _Cutoff =

    DATE(

        YEAR(_CurrentMonthStart),

        MONTH(_CurrentMonthStart),

        15

    )

VAR _LatestStampInScope =

    CALCULATE(

        MAX(Data[SystemModstamp]),

        FILTER(

            Data,

            Data[Rep] = EARLIER(Data[Rep]) &&

            Data[Product] = EARLIER(Data[Product]) &&

            YEAR(RELATED(Calendar[Date])) = YEAR(_CurrentMonthStart) &&

            MONTH(RELATED(Calendar[Date])) = MONTH(_CurrentMonthStart) &&

            Data[SystemModstamp] >= _CurrentMonthStart &&

            Data[SystemModstamp] <= _Cutoff

        )

    )

RETURN

IF(

    Data[SystemModstamp] = _LatestStampInScope,

    Data[Amount],  -- replace with Forecast column if different

    0

)

Thank you!!

[etane]

Hi @v-sathmakuri .

This is an excellent measure.  I did find an issue when I applied it to my working file.  

The forecast below should be 140,000.  But, because the systemmodstamp date and time is the same for both rows, the measure is combining the two forecast into a single one, 290,000.

Would you be able to tweak the measure so that it takes the Validfromdate as a tie breaker, so only the row with the later Validfromdate forecast row is picked up when there are identical Sysmoddates?

Edit:  Problem solved by using max Validfromdate while still using sysmoddate for cutoff.

[v-sathmakuri]

Hi @etane ,

Thank you for confirming your issue got resolved by making small changes.

Thanks!!

[rohit1991]

Hi @etane 

Could you please follow these steps below :

Create Two Measures:

Forecast15thLatest =
VAR MonthDate = MAX ( 'Calendar'[Date] )
VAR Cutoff    = DATE ( YEAR ( MonthDate ), MONTH ( MonthDate ), 15 )
VAR vRep      = SELECTEDVALUE ( Data[Rep] )
VAR vProd     = SELECTEDVALUE ( Data[Product] )
RETURN
IF (
   NOT ISBLANK ( vRep ) && NOT ISBLANK ( vProd ),
   VAR OneRow =
       CALCULATETABLE (
           TOPN (
               1,
               FILTER (
                  ALL ( Data ),
                  Data[Rep] = vRep
                      && Data[Product] = vProd
                      && YEAR ( Data[SystemModstamp] ) = YEAR ( MonthDate )
                      && MONTH ( Data[SystemModstamp] ) = MONTH ( MonthDate )
                      && Data[SystemModstamp] <= Cutoff
               ),
              Data[SystemModstamp], DESC
           )
       )
   RETURN
       MAXX ( OneRow, Data[Amount] )
)

Forecast Selected =
VAR Base = [Forecast15thLatest]
RETURN
SWITCH (
   TRUE(),
   ISINSCOPE ( Data[Product] ),                         
       Base,
   ISINSCOPE ( Data[Rep] ),                             
       SUMX ( VALUES ( Data[Product] ), Base ),
   SUMX ( SUMMARIZE ( Data, Data[Rep], Data[Product] ), [Forecast15thLatest] )
)

Add Forecast Selected into your Matrix.