Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

date diff-row count if condition apply

Dear Friends

 

i have two dates column date1 and date2,

 

and calculatting the difference by measure by comparing constant value Z=3:

 

Measure =
var z=3
VAR diff=SUMX(Sheet1,DATEDIFF(Sheet1[date1].[Date],Sheet1[date2].[Date],DAY))
var result =IF(diff>z,1,0)
return result
 
its appearing in the table list but can not get total at last or in Card
 

 

 
How can i solve this through measures (not by creating calculated column)
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    Rename your current measure to be Measure Individual and then create a new measure

    Measure = IF( ISINSCOPE( 'Table'[date1]), [Measure Individual],
SUMX( ADDCOLUMNS( SUMMARIZE( 'Table', 'Table'[date1], 'Table'[date2]),
   "@val", [Measure Individual] ),
[@val] )
)

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    Rename your current measure to be Measure Individual and then create a new measure

    Measure = IF( ISINSCOPE( 'Table'[date1]), [Measure Individual],
SUMX( ADDCOLUMNS( SUMMARIZE( 'Table', 'Table'[date1], 'Table'[date2]),
   "@val", [Measure Individual] ),
[@val] )
)
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    Create a measure.

    Measure =
VAR _Count =
    COUNTROWS (
        FILTER ( 'Table', DATEDIFF ( 'Table'[date1], 'Table'[date2], DAY ) > 3 )
    )
RETURN
    IF ( ISBLANK ( _Count ), 0, _Count )

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.