Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help with comparting data dates

Hi,   I would like to do some calculations with the below data. YTD - which I know I could use the TOTALYTD + Filter   TotalYTD - Actual = TOTALYTD(Sum('Table'[GM]),'Table'[Full Date],FILTER('Ta...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    The formula in your lastest post is correct to solve this problem.

    So far, it is a useful workaround for your problem.

     

    The penultimate one shows a incorrect formula.

    MinDate =
    CALCULATE (
     MIN ( 'Table'[Full Date] ),
     FILTER ( 'Table', 'Table'[Measure Version] = "Actual" ),
     FILTER ( 'Table', 'Table'[Full Date] = YEAR ( 2018 ) )  //incorrect 
    )

    Please see reference how to use "calculate" with "filter"

    https://www.sqlbi.com/articles/filter-arguments-in-calculate/

     

    If the formula is used in a measure, you could use the following instead.

    YTD Plan =
VAR TableMaxDate =
    CALCULATE (
        MAX ( 'Table'[Full Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Measure Version] = "Actual" )
    )
VAR MinDate =
    CALCULATE (
        MIN ( 'Table'[Full Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Measure Version] = "Actual"
                && YEAR ( 'Table'[Full Date] ) = 2018  //from the information, it seems it is no 
                                                             need to add this part, if so, 
                                                             you could delete this part    
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[GM] ),
        FILTER ( 'Table', 'Table'[Measure Version] = "Plan" ),
        DATESBETWEEN ( DIM_Date[Date], MinDate, TableMaxDate )
    )

    Best Regards

    Maggie

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Jeltex,

 

I have just tried this;

 

 

Last Year = 
CALCULATE (
    [TotalYTD - Actual],
    SAMEPERIODLASTYEAR ( 'Table'[Full Date] )
)

 

 

But i get this error when i bring in other columns;

 

MdxScript(Model) (12, 5) Calculation error in measure 'Table'[Last Year]: Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion. 

 

I am guessing that this is due to me not having contiguous date data, but i have not really used a date table before.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Try creating a simple date dimension by using add table and then the following code:

DIM_Date = CALENDARAUTO()

Then set up a relationship between 'DIM_Date'[Date] and 'Table'[Full Date].

 

Then change your code to:

Last Year = 
CALCULATE (
    [TotalYTD - Actual],
    SAMEPERIODLASTYEAR ( 'DIM_Date'[Date] )
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    That now works but i have noticed that it is not the same period,

     

    It looks like it is doing the whole year and not just the same date range as the main TOTALYTD

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Try using the following code:

      YTD LY = 
VAR TableMaxDate=
    CALCULATE ( MAX ( 'Table'[Full Date] ), ALL ( Table ) )
RETURN
    CALCULATE (
        [TotalYTD - Actual],
        SAMEPERIODLASTYEAR (
            INTERSECT (
                VALUES ( 'DIM_Date'[Date] ),
                DATESBETWEEN ( 'DIM_Date'[Date], BLANK (), TableMaxDate )
            )
        )
    )
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Sadly that comes back with the same answer as ;

         

        Last Year = 
CALCULATE (
    [TotalYTD - Actual],
    SAMEPERIODLASTYEAR ( DIM_Date[Date] )
)