Help with comparting data dates

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.

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

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

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

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

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

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