Forum Discussion

sarikei's avatar
sarikei
Helper I
3 years ago

Calculate record within selected dataset

Hi,

 

I am using the following formula in my measure to count number of Sales Document that have been processed per month:

 

# Sales Document Rolling =

    CALCULATE(

        SalesTable[# Sales Document Processed],

        DATESINPERIOD(

            DateTable[Date],

            MAX(DateTable[Date]),

            -1,

            MONTH

       ),

       USERELATIONSHIP(

            SalesTable[DocumentProcessedDate], DateTable[Date]

        )

    )

 

This return the correct result; it gives me the number of Sales Document that have been processed in the selection month (using Document Processed Date).

However, how can I include another condition here to filter only those Sales Document that are created in the selected month?

I have Document Created Date in my SalesTable.  So, it should be:

 

  1. Select all the Sales Document created in the selected month (using Document Created Date).
  2. Then calculated how many Sales Document have been processed (using DocumentProcessedDate).

 

 

Please help.

 

Thank you.

1 Reply

  • sarikei , In case # Sales Document Processed is a measure. Do not use sum function. Try a measure like

     

    # Sales Document Rolling =

    CALCULATE( CALCULATE(

    Sum('SalesTable'[# Sales Document Processed]),USERELATIONSHIP(

    SalesTable[DocumentProcessedDate], DateTable[Date]

    )

    DATESINPERIOD(

    DateTable[Date],

    MAX(DateTable[Date]),

    -1,

    MONTH

    ),


    ))