Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate date filter

Hello,    I ve been trying to improve my calculation with additional date filter, but it doesnt make any diff. It behaves like it calculates the result for all dates   Everything is calcula...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 
    Please try

    Yield Mech. assemblyprevmonth =
VAR CurrentYearMonth =
    MAX ( 'Date'[Year Month Number] )
VAR MechAssembly =
    CALCULATETABLE (
        TJob,
        TJob[SekundærNavn] = "Mech. assembly",
        'Date'[Year Month Number] = CurrentYearMonth - 1,
        ALL ( 'Date' )
    )
RETURN
    ROUND (
        DIVIDE (
            SUMX ( MechAssembly, TJob[FirstPassQty] ),
            SUMX ( MechAssembly, TJob[Antal] )
        ),
        4
    )
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Idk know why but the measure keeps displaying blank after previous month function. So as few others i had try for example

 

Date column is correctly formatted as date, rest as whole number.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Could you reference measure to VAR version. Contoso model looks very good, refering to year month number is pretty nifty idea, but when i tried to copy results....

     

     

    It still wont give me the previous month

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Anonymous 
      Can you copy/paste this code so I can modify?

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago
        Yield Mech. assemblyprevmonth =
        VAR MechAssembly =
            FILTER ( TJob, TJob[SekundærNavn] = "Mech. assembly" )
        RETURN
            CALCULATE(ROUND(
                DIVIDE (
                    SUMX ( MechAssembly, TJob[FirstPassQty] ),
                    SUMX ( MechAssembly, TJob[Antal] ) ),
                    4),
                    ALL('Date'),
                    'Date'[Year Month Number] = MAX('Date'[Year Month Number]) -1)