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

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!

    • revansh's avatar
      revansh
      Helper IV
      9 years ago

      Hi ,

       

      Actually my data is like this.

       

      01-jan-15           1.5

      01-feb-15           1.0

      01-mar-15          2.0

      01-jan-16            1.0

      01-feb-16            0

      01-mar-16            0

      01-jan-17           1.0

      01-feb-17           2.0

      01-mar-17           0

       

      My output data should be like:

       

      01-mar-15     2.0

      01-mar-16     1.0 ( since mar16 and feb16 value =0 , i considered jan16 value)

      01-mar-17      2.0 ( since mar17 value=0 , i considere prior month value i.e., 2.0)

       

      hope it is clear.

       

      Thanks for your valuable inputs so far.

       

      Thanks

      • SqlJason's avatar
        SqlJason
        Memorable Member
        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