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

Could you elaborate more? You mean putting sumx as VAR?

 

Yield Mech. assembly =
VAR nom =
    CALCULATE( SUMX(TJob, TJob[Antal]), TJob[SekundærNavn] = "Mech. assembly" , PREVIOUSMONTH(TJob[Dato]))
VAR den =
    CALCULATE(SUMX(TJob, TJob[FirstPassQty]), TJob[SekundærNavn] = "Mech. assembly", PREVIOUSMONTH(TJob[Dato]))
RETURN
    ROUND(
        DIVIDE (
            den,
             nom),
            4)

 

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

Anonymous 

Please try

Yield Mech. assembly111 =
CALCULATE (
VAR MechAssembly =
FILTER ( TJob, TJob[SekundarNavn] = "Mech. assembly" )
RETURN
ROUND (
DIVIDE (
SUMX ( MechAssembly, TJob[Antal] ),
SUMX ( MechAssembly, TJob[FirstPassQty] )
),
4
),
PREVIOUSMONTH ( TJob[Dato] )
)

  • 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