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
Or even easier,
month order = VALUE(FORMAT(Calendar[Date], "YYYYMM"))
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

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    revansh

    Okay using your sample data I created a Table called 'Table'

    with columns Date and Value then...

    1) Create a Year COLUMN

    Year = YEAR('Table'[Date])

    2) Sales MEASURE

    Sales = SUM('Table'[Value])

    3) Another MEASURE

    Last Date (Sales > 0) = 
CALCULATE (
    LASTDATE ( 'Table'[Date] ),
    VALUES ( 'Table'[Year] ), 
    FILTER ( 'Table', [Sales] > 0 ) 
)

    4) and Final MEASURE

    Sales on Last Date = 
CALCULATE (
    [Sales],
    FILTER (
        'Table',
        'Table'[Date]
            = CALCULATE (
                LASTDATE ( 'Table'[Date] ),
                VALUES ( 'Table'[Year] ),
                FILTER ( 'Table', [Sales] > 0 )
            )
    )
)

    Obviously you can rename all of these...

    Here's the result - all sample data on the left and desired outcome in the table on the right

    which is a Table Visual with the Year Column and the Last Date (Sales > 0) and KPI on Last Date Measures

     

    Hope this helps! :smileyhappy:

  • 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

  • revansh's avatar
    revansh
    Helper IV
    9 years ago

    Hi sqlJason,

     

    i was able to complete the requirement using DAX functon you suggested.  i want to enhance the dax function.In the variable section filters field i want to pass parameter based on user selection. 

     

    Eg: there is a slicer in the report : 

     

    REGION :   East West North South

    Based on this selection(say if the user clicked on WEST , i want to dynamically change the VARIABLE as :

     

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

     is this possible.

     

    Thanks

     

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

  • revansh's avatar
    revansh
    Helper IV
    9 years ago

    Got it... Applied your solution and its working perfect.

     

    Thank you Very much