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

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