Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate Mutiple date

Hello,

I have searched many forums and could not find an answer, I hope you can help me.

I am posting an example of my data below :

I also have a table "Calendrier", that is a calendar table.

There is an inactive relationship between TOP[START] and CALENDRIER[Date] and an active relationship between TOP[END] and CALENDRIER[Date].

 

What I need to do is the following:
I would like to count the number of things with an end date for the current month and the next month AND that have a start date in the current month (and that have a type other than 1).
Example: For the month of March, I want all those that have a start date in March AND an end date in March OR April (and this for all months).
I tried the following:
- I create a column that calculates the next end month: end_month_after = dateadd(TOP[END],-1,MONTH)
- I create a measure that calculates all those created for the current month: measure1=CALCULATE(DISTINCTCOUNT(TOP[type]),USERELATIONSHIP(Calendar[Date],TOP[START]))
- I create a second measure which uses the first one : calculate(measure1,USERELATIONSHIP(Calendar[Date],TOP[END])+calculate(measure1,USERELATIONSHIP(Calendar[Date],TOP[end_month_after ])

But it doesn't work. It keeps the start dates but not the end dates; can you help me?

Thank's

Victoria

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    Try changing it to

    My Measure =
VAR ReferenceDate =
    MAX ( 'Calendar'[Date] )
VAR StartDate =
    EOMONTH ( ReferenceDate, -1 ) + 1
VAR EndNextMonth =
    EOMONTH ( ReferenceDate, 1 )
VAR EndThisMonth =
    EOMONTH ( ReferenceDate, 0 )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Top' ),
        REMOVEFILTERS ( 'Calendar' ),
        TREATAS (
            DATESBETWEEN ( 'Calendar'[Date], StartDate, EndNextMonth ),
            'Top'[End date]
        ),
        TREATAS (
            DATESBETWEEN ( 'Calendar'[Date], StartDate, EndThisMonth ),
            'Top'[Start date]
        ),
        'Top'[Type] <> 1
    )
RETURN
    Result

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    Try

    My Measure =
VAR ReferenceDate =
    MAX ( 'Calendar'[Date] )
VAR StartDate =
    EOMONTH ( ReferenceDate, -1 ) + 1
VAR EndNextMonth =
    EOMONTH ( ReferenceDate, 1 )
VAR EndThisMonth =
    EOMONTH ( ReferenceDate, 0 )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Top' ),
        REMOVEFILTERS ( 'Calendar' ),
        'Top'[End date] IN DATESBETWEEN ( 'Calendar'[Date], StartDate, EndNextMonth ),
        'Top'[Start date] IN DATESBETWEEN ( 'Calendar'[Date], StartDate, EndThisMonth ),
        'Top'[Type] <> 1
    )
RETURN
    Result
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      It doesn't work... it tells me the following error: A "DATESBETWEEN" function was used in a True/False expression used as a table filter expression. This is not allowed.
      But thank you very much for your help

      • johnt75's avatar
        johnt75
        Super User
        3 years ago

        Try changing it to

        My Measure =
VAR ReferenceDate =
    MAX ( 'Calendar'[Date] )
VAR StartDate =
    EOMONTH ( ReferenceDate, -1 ) + 1
VAR EndNextMonth =
    EOMONTH ( ReferenceDate, 1 )
VAR EndThisMonth =
    EOMONTH ( ReferenceDate, 0 )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Top' ),
        REMOVEFILTERS ( 'Calendar' ),
        TREATAS (
            DATESBETWEEN ( 'Calendar'[Date], StartDate, EndNextMonth ),
            'Top'[End date]
        ),
        TREATAS (
            DATESBETWEEN ( 'Calendar'[Date], StartDate, EndThisMonth ),
            'Top'[Start date]
        ),
        'Top'[Type] <> 1
    )
RETURN
    Result