Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |