Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 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.
Try creating a simple date dimension by using add table and then the following code:
DIM_Date = CALENDARAUTO()
Then set up a relationship between 'DIM_Date'[Date] and 'Table'[Full Date].
Then change your code to:
Last Year = CALCULATE ( [TotalYTD - Actual], SAMEPERIODLASTYEAR ( 'DIM_Date'[Date] ) )
That now works but i have noticed that it is not the same period,
It looks like it is doing the whole year and not just the same date range as the main TOTALYTD
Try using the following code:
YTD LY = VAR TableMaxDate= CALCULATE ( MAX ( 'Table'[Full Date] ), ALL ( Table ) ) RETURN CALCULATE ( [TotalYTD - Actual], SAMEPERIODLASTYEAR ( INTERSECT ( VALUES ( 'DIM_Date'[Date] ), DATESBETWEEN ( 'DIM_Date'[Date], BLANK (), TableMaxDate ) ) ) )
Sadly that comes back with the same answer as ;
Last Year = CALCULATE ( [TotalYTD - Actual], SAMEPERIODLASTYEAR ( DIM_Date[Date] ) )
Try changing your totalYTD code to the following:
TotalYTD - Actual = IF ( MIN ( 'DIM_Date'[Date] ) <= CALCULATE ( MAX ( 'Table'[Full Date] ), ALL ( Table) ), TOTALYTD(Sum('Table'[GM]),'Table'[Full Date],FILTER(Table,'Table'[Measure Version]="Actual")) )
And can you post a pictore of your results? Then I get a bit more information on what is happening on your side.
Hi,
I have tried to copy your code but I am getting this error
I forgot to place the "=" in the measure. So now it sees the measure as the measure name instead of the actual measure.
TotalYTD - Actual = IF ( MIN('DIM_Date'[Date]) <= CALCULATE(MAX('Table'[Full Date]),ALL('Table'), TOTALYTD(Sum('Table'[GM]),'Table'[Full Date],FILTER(Table, 'Table'[Measure Version]="Actual")) )
arrr yes! will test now 🙂
I am getting this error i get when i add the =
@AnonymousAny joy with this?
Do you think that this is the correct approach to the problem?
As I also have the same issue where I will need to look at the same date range for this year for the Plan and RF data?
Thanks
Hi,
The syntax error is in this part
FILTER('Table', etc....... It needs to be FILTER(Table, etc....
I tried to recreate your question and I got this:
Are the left two columns the result that you want?
Hi @Anonymous
I would like it to show the data without the missing actual data, from this i would also do a variance formula between all of the values.
So the highlighted red section is never shown and also i get a true YTD compared with the current YTD.
Thanks
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
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 @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
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |