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,

 

I think you can solve your problem with the following measures:

YTD = 
IF (
    MIN ( 'DIM_Date'[Date] ) <= CALCULATE ( MAX ( 'Table'[Full Date] ), ALL ( Table) ),
    CALCULATE(Sum('Table'[GM]),DATESYTD('DIM_Date'[Date]))
)
YTD LY = 
VAR TableMaxDate=
    CALCULATE ( MAX ( 'Table'[Full Date] ), ALL ( Table) )
RETURN
    CALCULATE (
        [YTD],
        SAMEPERIODLASTYEAR (
            INTERSECT (
                VALUES ( 'DIM_Date'[Date] ),
                DATESBETWEEN ( 'DIM_Date'[Date], BLANK (), TableMaxDate )
            )
        )
    )

You can then use a matrix visual and place DIM_Date[Date] in the rows and table[Measure Version] on columns.

 

I think that that will give your desired result.

 

You can also make your own filter measures with

YTD Filtered = CALCULATE([YTD],FILTER(Table,'Table'[Measure Version] = "@Filtered measure version"

And replace the [YTD] in the YTD LY with the YTD Filtered. But this means that you have to create 7 measures in total instead of 2.

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 :)