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

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi Anonymous

Does the screenshot of your last reply not completely achieve your goal?

The rows of 2018/10, 2018/11, 2018/12 for the "YTD LY", "YDT RF" and "YDT Plan" should be blank?

 

 

Best Regards

Maggie

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-juanli-msft

Really I want to see the below in the red.

 

From this, I would also work out the difference between them all to the actual ytd values.

 

Thanks

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    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 v-juanli-msft and Anonymous

     

    Thank you for all of your help!

     

    I think I am on the right track now!

    Can you check the below that I have gone around this the correct way and are not just making problems for myself in the future!

     

     

    YTD LY = 
VAR TableMaxDate=
    CALCULATE ( MAX ( 'Table'[Full Date] ), FILTER('Table','Table'[Measure Version]="Actual" ) )
VAR MinDate=
    CALCULATE ( MIN ( 'Table'[Full Date] ), FILTER('Table',TableMaxDate=YEAR(TableMaxDate)),FILTER('Table','Table'[Measure Version]="Actual"))
RETURN
    CALCULATE (
        [YTD],FILTER('Table','Table'[Measure Version]="Actual"),
        SAMEPERIODLASTYEAR (
            INTERSECT (
                VALUES ( 'DIM_Date'[Date] ),
                DATESBETWEEN ( 'DIM_Date'[Date], MinDate , TableMaxDate )
            )
        )
    )

    This gives me the correct results :)

     

    Thanks

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-juanli-msft and Anonymous

     

    I have been trying to refactor the RF and Plan measures to fit this new way of working into it but it seems not to work.

     

     

    YTD Plan = 
VAR TableMaxDate=
    CALCULATE ( MAX ( 'Table'[Full Date] ), FILTER('Table','Table'[Measure Version]="Actual" ) )
VAR MinDate=
    CALCULATE ( MIN ( 'Table'[Full Date] ), FILTER('Table','Table'[Measure Version]="Actual"),FILTER('Table','Table'[Full Date]=YEAR(2018)))
RETURN
    CALCULATE (
        Sum ('Table'[GM]), FILTER('Table','Table'[Measure Version]="Plan" ),DATESBETWEEN (DIM_Date[Date], MinDate , TableMaxDate
            ))

    I think it does not like the MinDate, as when I try to create a single Measure to see if this works it is blank.

     

     

    Min = 
Var tmax = CALCULATE ( MAX ( 'Table'[Full Date] ), FILTER('Table','Table'[Measure Version]="Actual"))
Return 
CALCULATE ( MIN ( 'Table'[Full Date] ), FILTER('Table','Table'[Measure Version]="Actual"),FILTER('Table','Table'[Full Date]=YEAR(tmax))
)

    Thanks

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Ok, I have done the below;

     

     

    Max Year Number = CALCULATE ( MAX ( 'Table'[Year] ), FILTER('Table','Table'[Measure Version]="Actual"))

     

     

     

    YTD Plan = 
VAR TableMaxDate=
    CALCULATE ( MAX ( 'Table'[Full Date] ), FILTER('Table','Table'[Measure Version]="Actual" ) )
RETURN
    CALCULATE (
        Sum ('Table'[GM]), FILTER('Table','Table'[Measure Version]="Plan" ),DATESBETWEEN (DIM_Date[Date], DATE([Max Year Number],1,1) , TableMaxDate
            ))

    It seems a little bit messy way of doing things but it got the right answer!

     

     

    Do you think there is a better way to do this?

     

    Thanks