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.  

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?

parry2k's avatar
parry2k
Super User
9 years ago

That is where it is helpful to understand your tables, if you have continuous calendar table, it will help to make time based calculation easy. In case you don't have calendar table, here is the link on how you can create one and link your data table to calendar table.

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    Take your pick :smileyhappy:

     

    Month Order =
INT (
    CONCATENATE (
        YEAR ( 'Calendar'[Date] ),
        CONCATENATE (
            IF ( MONTH ( 'Calendar'[Date] ) < 10, "0", "" ),
            MONTH ( 'Calendar'[Date] )
        )
    )
)

     or the easier version

     

    Month Order 2 = 
INT (
    CONCATENATE ( YEAR ( 'Calendar'[Date] ), FORMAT ( 'Calendar'[Date], "MM" ) )
)

    or the easiest version :smileyhappy:

     

    Month Order 3 = 
    VALUE ( FORMAT ( Calendar[Date], "YYYYMM" ) )

    All 3 do the same! :smileyhappy:

    • SqlJason's avatar
      SqlJason
      Memorable Member
      9 years ago
      Or even easier,
      month order = VALUE(FORMAT(Calendar[Date], "YYYYMM"))
      • Sean's avatar
        Sean
        Community Champion
        9 years ago

        :smileylol: SqlJason you could not resist typing this on your phone :smileyvery-happy:

         

        but yes it is easier indeed :smileyvery-happy: for some reason the font is smaller when you respond from a phone!