Forum Discussion
Dax Measure help
DateDimensionID JobCloseDate PolicyNumber BranchID Job 20160701 7/1/2016 0:00 A 1 Submission 20160702 7/2/2016 0:00 A 3 Policy Change 20160702 7/3/2016 1:00 C 4 Reinstatem...
In you provided table, the type of DateDimensionID is not standard Date type, so I create a new date column with following formula and create relationship with calendar table with this column.
Date = DATE ( LEFT ( Table1[DateDimensionID], 4 ), MID ( Table1[DateDimensionID], 5, 2 ), MID ( Table1[DateDimensionID], 7, 2 ) )
Then we can create another measure to display the result in the card.
Measure =
VAR CountCurrentDay =
CALCULATE (
DISTINCTCOUNT ( Table1[PolicyNumber] ),
FILTER (
ALL ( Table1 ),
Table1[Date] = MAX ( Td_Date[Date] )
&& Table1[Job] = "Cancellation"
)
)
RETURN
(
IF (
CountCurrentDay > 0,
CountCurrentDay,
CALCULATE (
DISTINCTCOUNT ( Table1[PolicyNumber] ),
FILTER (
ALL ( Table1 ),
Table1[JobCloseDate]
= CALCULATE (
MAX ( Table1[JobCloseDate] ),
FILTER ( ALL ( Table1 ), Table1[JobCloseDate] < MAX ( Table1[Date] ) )
)
&& Table1[Job] = "Cancellation"
)
)
)
)
Best Regards,
Herbert
Thank you for ur detail reply.
What i would like to see is
on 07/04/2016 it should show me 2
and not 1
if I select a 07/04/2016. It should count all the policies from the start to that specific date(which is 07/04/2016)
Please try to use the following updated measure.
Measure_Update = VAR CountCurrentDay = CALCULATE ( DISTINCTCOUNT ( Table1[PolicyNumber] ), FILTER ( ALL ( Table1 ), Table1[Date] = MAX ( Td_Date[Date] ) && Table1[Job] = "Cancellation" ) ) RETURN ( IF ( CountCurrentDay > 0, CountCurrentDay, CALCULATE ( DISTINCTCOUNT ( Table1[PolicyNumber] ), FILTER ( ALL ( Table1 ), Table1[Date] < MAX ( Td_Date[Date] ) && Table1[Job] = "Cancellation" ) ) ) )Best Regards,
Herbert