Forum Discussion

dexter2424's avatar
dexter2424
Icon for Helper I rankHelper I
5 years ago
Solved

Distinct Count Total issue based on Type and latest period

Hi All,    I'm struggling with an issue, maybe it's easy to solve, or not, but I'm get lost, and the distinctcount doesn't work properly for me. I have the following table Policy Number Rep...
Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    dexter2424,

     

    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:

     

     

    ---------------------------------------------

     

     

    ---------------------------------------------

     

     

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
5 years ago

dexter2424,

 

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:

 

 

---------------------------------------------

 

 

---------------------------------------------

 

 

 

  • dexter2424's avatar
    dexter2424
    Icon for Helper I rankHelper I
    5 years ago

    Thanks you DataInsights! That's what I'm looking for.

    In the meantime I have figured out another solution what is working, I just leave it here, maybe somebody findes it useful:

    Creating a max date measure:

    Max_Date = CALCULATE(MAXX(Table,Table[Reporting Period]),ALLEXCEPT(Table,Table[Policy Number]))

    and the main measure:

    Large = COUNTX(FILTER(Table,Table[Type] = "Large" && Table[Reporting Period] = [Max_Date]),Table[Policy Number])



  • Mi_80's avatar
    Mi_80
    Icon for Helper I rankHelper I
    3 years ago

    Hi,

     

    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