Forum Discussion

CarlijnM's avatar
CarlijnM
Frequent Visitor
4 years ago
Solved

Count active dossiers per month

Hi,

 

Hope somebody can help me! I have data with dossiers that have a startdate, but no enddate and dossiers that have a start and end date. This is the data:

DossiernumberStartdateEnddate
104-02-2008 
210-05-2010 
322-11-201818-04-2022
431-05-202131-12-2021

 

I would like an output that counts the number of dossiers that were active in a month. For example, the startdate is 01 jan 2022 and the enddate is 31 mar 2022 I would like the table to present a follows:

JanFebMarApr
1110

 

I have Calander table that is linked to the data.

 

I have a Measure that shows the cumulative number of dossiers: 

Cumulatief dossiers = CALCULATE([Aantal dossiers], FILTER(ALL('Kalender'), Kalender[Datum] <= MAX(Kalender[Datum])))
 
I can't figure out how to filter on enddate.

 

I searched a lot and tried a lot of things that I found, but it does not give me the outcome that I am looking for.

 

Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    CarlijnM,

     

    Try this solution. It uses a secondary date table DimDateVisual that has no relationship to the fact table.

     

    Create measure:

     

    Active Dossiers = 
VAR vMinDate =
    MIN ( DimDateVisual[Date] )
VAR vMaxDate =
    MAX ( DimDateVisual[Date] )
VAR vTable =
    FILTER (
        FactTable,
        VAR vEndDate =
            IF ( ISBLANK ( FactTable[Enddate] ), DATE ( 9999, 12, 31 ), FactTable[Enddate] )
        RETURN
            FactTable[Startdate] <= vMaxDate
                && vEndDate >= vMinDate
    )
VAR vResult =
    CALCULATE ( COUNT ( FactTable[Dossiernumber] ), vTable )
RETURN
    vResult

     

    Data:

     

     

    The matrix columns should use DimDateVisual[Month Year]:

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    4 years ago

    CarlijnM,

     

    Try this solution. It uses a secondary date table DimDateVisual that has no relationship to the fact table.

     

    Create measure:

     

    Active Dossiers = 
VAR vMinDate =
    MIN ( DimDateVisual[Date] )
VAR vMaxDate =
    MAX ( DimDateVisual[Date] )
VAR vTable =
    FILTER (
        FactTable,
        VAR vEndDate =
            IF ( ISBLANK ( FactTable[Enddate] ), DATE ( 9999, 12, 31 ), FactTable[Enddate] )
        RETURN
            FactTable[Startdate] <= vMaxDate
                && vEndDate >= vMinDate
    )
VAR vResult =
    CALCULATE ( COUNT ( FactTable[Dossiernumber] ), vTable )
RETURN
    vResult

     

    Data:

     

     

    The matrix columns should use DimDateVisual[Month Year]: