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 Anonymous

Great help, I think I am starting to understand this now!

But when I have done this it is nearly there, but it looks like below;

When working out the date range for the YTD's it will only be for the Actual, As RF and Plan will always have future data.

 

 

Thank you for all of your help :)

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

  • 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

    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

  • 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