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

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

      Try changing your totalYTD code to the following:

      TotalYTD - Actual =
IF (
    MIN ( 'DIM_Date'[Date] ) <= CALCULATE ( MAX ( 'Table'[Full Date] ), ALL ( Table) ),
TOTALYTD(Sum('Table'[GM]),'Table'[Full Date],FILTER(Table,'Table'[Measure Version]="Actual"))
)

      And can you post a pictore of your results? Then I get a bit more information on what is happening on your side.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi,

         

        I have tried to copy your code but I am getting this error