Forum Discussion
Helper IDistinct Count Total issue based on Type and latest period
Try this solution.
1. Create date table with a relationship to the data table:
2. Create measure:
Large = VAR vSlicerEndDate = LASTDATE ( ALLSELECTED ( Dates[Date] ) ) VAR vResult = SUMX ( VALUES ( FactTable[Policy Number] ), VAR vMaxDate = CALCULATE ( MAX ( FactTable[Reporting Period] ), FactTable[Reporting Period] <= vSlicerEndDate ) VAR vType = CALCULATE ( MAX ( FactTable[Type] ), FactTable[Reporting Period] = vMaxDate ) RETURN IF ( vType = "Large", 1, 0 ) ) RETURN vResult3. Create date slicer using the date table (Dates[Date]).
4. Create visual:
---------------------------------------------
---------------------------------------------
Super User
Try this solution.
1. Create date table with a relationship to the data table:
2. Create measure:
Large =
VAR vSlicerEndDate =
LASTDATE ( ALLSELECTED ( Dates[Date] ) )
VAR vResult =
SUMX (
VALUES ( FactTable[Policy Number] ),
VAR vMaxDate =
CALCULATE (
MAX ( FactTable[Reporting Period] ),
FactTable[Reporting Period] <= vSlicerEndDate
)
VAR vType =
CALCULATE ( MAX ( FactTable[Type] ), FactTable[Reporting Period] = vMaxDate )
RETURN
IF ( vType = "Large", 1, 0 )
)
RETURN
vResult
3. Create date slicer using the date table (Dates[Date]).
4. Create visual:
---------------------------------------------
---------------------------------------------
Helper IHi,
I am using this solution and it works great thanks. I have a quick question. If I wanted to display this measure in a bar chart instead with the value for each month acros x axis how could that be achieved? Basically I would like to not have the slider but do the calculation for using the value of the first date of each month instead. Thanks