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
    )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity 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

You re a God!

 

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

  • 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
    Icon for Community Champion rankCommunity 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
    Icon for Community Champion rankCommunity 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.