Forum Discussion
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('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
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
22 Replies
Hi Jeltex,
I have just tried this;
Last Year = CALCULATE ( [TotalYTD - Actual], SAMEPERIODLASTYEAR ( 'Table'[Full Date] ) )But i get this error when i bring in other columns;
MdxScript(Model) (12, 5) Calculation error in measure 'Table'[Last Year]: Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.
I am guessing that this is due to me not having contiguous date data, but i have not really used a date table before.