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

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

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: