|20160702||7/2/2016 0:00||A||3||Policy Change|
I am trying to write a measure in tabular which gets me count of all the policies whose latest Job is "Cancellation"
latest Job is based on JobCloseDate.
IN Power BI Desktop If I have a slicer on Td_Date[CalendarDate](It is connected to this table using DateDimensionID)
If I select 07/04/2016 on the slicer then the totalCount should be 1
If I select 07/04/2016 on the slicer then the totalCount should be 2
I was able to write the dax in Dax Studio but not able to put it in tabular model.
Kindly help me out on this.
According to your description, you want to count policies if the Job on LatestCloseDate is "Cancellation" on DateDimensionID wise. Right?
In this scenario, you can create a calcualted column to determine if current row can be counted as 1 with formula below:
Column = IF( CALCULATE(MAX(Table4[JobCloseDate]),ALLEXCEPT(Table4,Table4[PolicyNumber],Table4[DateDimensionID]))=Table4[JobCloseDate] && Table4[Job]="Cancellation",1,0)
Then you just need to create a measure to sum this column.
It can show correct result when filtered with DateDimensionID.
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)