Forum Discussion

revansh's avatar
revansh
Helper IV
9 years ago
Solved

DAX FUNCTION

Hi All,   My requirement is to show current month data as a KPI value   Sample Data1:   JAN   0.1 FEB    0.2 Mar   0.3 Apr    0.4   KPI Value = 0.4   Sample Data2:   JAN   0.1 FEB    ...
Need Help
  • SqlJason's avatar
    SqlJason
    9 years ago

    Assuming that Table1 is your table name, the first column is of date type (and is called Date) and there is a measure called Sales = sum(Table1[KPIValue]), then use the formula below

     

    Test =
    VAR LastDateWithSales =
     CALCULATE (
     MAX ( Table1[Date] ),
     FILTER ( ALL ( Table1 ), Table1[Date] <= MAX ( Table1[Date] ) && [Sales] > 0 )
     )
    RETURN
     CALCULATE ( [Sales], Table1[Date] = LastDateWithSales )

    i got the below result

  • SqlJason's avatar
    SqlJason
    9 years ago

    Is the Region coming from a master (lookup) table or is it part of the same Table? If part of the same table, you can just use an ALLEXCEPT

    VAR LastDateWithSales =
     CALCULATE (
     MAX ( Table1[Date] ),
     FILTER ( ALLEXCEPT ( Table1, Table1[Region] ), Table1[Date] <= MAX ( Table1[Date] ) && [Sales] > 0 )

    This will ensure that the LastDAte is calculated after the filter on Region is done. If Region is coming from a lookup table, you can use the ALLEXCEPT for the field in Table1 that is connecting to the Region lookup table.  

SqlJason's avatar
SqlJason
Memorable Member
9 years ago

It would be easier if you had given your model and table structure. 

 

What you have to do is to create a measure

 

KPI =
VAR MaxMonth =
    CALCULATE ( MAX ( Table[Month] ), FILTER ( ALL ( Table ), Table[Sales] > 0 ) )
RETURN
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER ( ALL ( Table ), Table[Month] = MaxMonth )
    )

 

  • parry2k's avatar
    parry2k
    Super User
    9 years ago

    You need to add a measure to you table with following formula and use myKPIValue measure to show the value.

     

    myKPIValue = CALCULATE(MAX(Table1[myValue]), FILTER(ALL(Table1), Table1[myValue]>0))

     

    • revansh's avatar
      revansh
      Helper IV
      9 years ago

      Hi Parry2k,

       

         we cant use max aggregation because value will not be in ascending order by month.

       

      Thanks

      • parry2k's avatar
        parry2k
        Super User
        9 years ago

        True, I missed that part. In the case SqlJason formula will work, Do you see any challenge with that?

  • revansh's avatar
    revansh
    Helper IV
    9 years ago

    Hi SqlJAson,

     

      what change needs to be done if i have to consider YEAR also.... MAX(table[month],table[year])?

     

    KPI =
VAR MaxMonth =
    CALCULATE ( MAX ( Table[Month] ), FILTER ( ALL ( Table ), Table[Sales] > 0 ) )
RETURN
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER ( ALL ( Table ), Table[Month] = MaxMonth )
    )

     Thanks

     

    • SqlJason's avatar
      SqlJason
      Memorable Member
      9 years ago

      Ideally, there should  be one field in the table which is a combination of Year and Month (if not, you can create it by concatenating year and month). And then use that inside the max.