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...
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
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
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      It works, thank you very much!