Help with comparting data dates

[Anonymous]

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('Table','Table'[Measure Version]="Actual"))

But how do I do a calculation with the same date range ie m January -  September 2018 for the Plan and RF?

Also the total of last year for the same date range January -  September 2017?

Measure Version	GM	Full Date
Actual	$810988387.85	01/01/2017 00:00
Actual	$337791028.13	01/02/2017 00:00
Actual	$550435475.76	01/03/2017 00:00
Actual	$402794574.66	01/04/2017 00:00
Actual	$467615215.98	01/05/2017 00:00
Actual	$540032362.83	01/06/2017 00:00
Actual	$431805160.25	01/07/2017 00:00
Actual	$463675935.83	01/08/2017 00:00
Actual	$501191739.41	01/09/2017 00:00
Actual	$438198301.68	01/11/2017 00:00
Actual	$396214086.60	01/12/2017 00:00
Actual	$259308342.49	01/01/2018 00:00
Actual	$328621377.27	01/02/2018 00:00
Actual	$446070845.37	01/03/2018 00:00
Actual	$357567501.36	01/04/2018 00:00
Actual	$456199138.76	01/05/2018 00:00
Actual	$421862533.37	01/06/2018 00:00
Actual	$392615815.82	01/07/2018 00:00
Actual	$465390953.83	01/08/2018 00:00
Actual	$442974962.65	01/09/2018 00:00
Plan	$913421956.57	01/01/2017 00:00
Plan	$412362821.45	01/02/2017 00:00
Plan	$518063594.23	01/03/2017 00:00
Plan	$556249173.68	01/04/2017 00:00
Plan	$524580575.38	01/05/2017 00:00
Plan	$555159147.85	01/06/2017 00:00
Plan	$526988132.04	01/07/2017 00:00
Plan	$552664759.97	01/08/2017 00:00
Plan	$605712510.44	01/09/2017 00:00
Plan	$587320990.59	01/11/2017 00:00
Plan	$532713634.62	01/12/2017 00:00
Plan	$937890299.60	01/01/2018 00:00
Plan	$336840287.06	01/02/2018 00:00
Plan	$467714233.55	01/03/2018 00:00
Plan	$456025669.16	01/04/2018 00:00
Plan	$472688772.28	01/05/2018 00:00
Plan	$550095419.86	01/06/2018 00:00
Plan	$551625717.89	01/07/2018 00:00
Plan	$574777728.59	01/08/2018 00:00
Plan	$653631927.13	01/09/2018 00:00
Plan	$650477750.50	01/11/2018 00:00
Plan	$549065649.02	01/12/2018 00:00
RF	$767205034.65	01/01/2018 00:00
RF	$328621377.27	01/02/2018 00:00
RF	$446070845.37	01/03/2018 00:00
RF	$357567501.36	01/04/2018 00:00
RF	$456199138.76	01/05/2018 00:00
RF	$421862533.37	01/06/2018 00:00
RF	$392615815.82	01/07/2018 00:00
RF	$465390953.83	01/08/2018 00:00
RF	$607816648.62	01/09/2018 00:00
RF	$541901652.92	01/11/2018 00:00
RF	$514735218.34	01/12/2018 00:00

Thank you 

[v-juanli-msft · Accepted solution]

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]

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