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 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
Community Champion
3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    And it returns blank. Well it doesnt matter right now. Thank you.

     

    To bad dax guides for time intelligence are showing easy results, nothing real life databases. At least for me

  • tamerj1's avatar
    tamerj1
    Community Champion
    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
    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)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    You re a God!

     

    The magic question what is the purpose of previousmonth function if its not working... or why is it not working

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Anonymous 
    It should work but I usually don't like to use time intellagence functions. You may try

    Yield Mech. assemblyprevmonth =
VAR CurrentYearMonth =
    MAX ( 'Date'[Year Month Number] )
VAR MechAssembly =
    CALCULATETABLE (
        TJob,
        TJob[SekundærNavn] = "Mech. assembly",
        PREVIOUSMONTH ( 'Date'[Date] )
    )
RETURN
    ROUND (
        DIVIDE (
            SUMX ( MechAssembly, TJob[FirstPassQty] ),
            SUMX ( MechAssembly, TJob[Antal] )
        ),
        4
    )
  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Anonymous 
    Forgot to mention that it returns blank if the date table is not filtered i.e. at the grand total level.