Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 VersionGMFull Date
Actual$810988387.8501/01/2017 00:00
Actual$337791028.1301/02/2017 00:00
Actual$550435475.7601/03/2017 00:00
Actual$402794574.6601/04/2017 00:00
Actual$467615215.9801/05/2017 00:00
Actual$540032362.8301/06/2017 00:00
Actual$431805160.2501/07/2017 00:00
Actual$463675935.8301/08/2017 00:00
Actual$501191739.4101/09/2017 00:00
Actual$438198301.6801/11/2017 00:00
Actual$396214086.6001/12/2017 00:00
Actual$259308342.4901/01/2018 00:00
Actual$328621377.2701/02/2018 00:00
Actual$446070845.3701/03/2018 00:00
Actual$357567501.3601/04/2018 00:00
Actual$456199138.7601/05/2018 00:00
Actual$421862533.3701/06/2018 00:00
Actual$392615815.8201/07/2018 00:00
Actual$465390953.8301/08/2018 00:00
Actual$442974962.6501/09/2018 00:00
Plan$913421956.5701/01/2017 00:00
Plan$412362821.4501/02/2017 00:00
Plan$518063594.2301/03/2017 00:00
Plan$556249173.6801/04/2017 00:00
Plan$524580575.3801/05/2017 00:00
Plan$555159147.8501/06/2017 00:00
Plan$526988132.0401/07/2017 00:00
Plan$552664759.9701/08/2017 00:00
Plan$605712510.4401/09/2017 00:00
Plan$587320990.5901/11/2017 00:00
Plan$532713634.6201/12/2017 00:00
Plan$937890299.6001/01/2018 00:00
Plan$336840287.0601/02/2018 00:00
Plan$467714233.5501/03/2018 00:00
Plan$456025669.1601/04/2018 00:00
Plan$472688772.2801/05/2018 00:00
Plan$550095419.8601/06/2018 00:00
Plan$551625717.8901/07/2018 00:00
Plan$574777728.5901/08/2018 00:00
Plan$653631927.1301/09/2018 00:00
Plan$650477750.5001/11/2018 00:00
Plan$549065649.0201/12/2018 00:00
RF$767205034.6501/01/2018 00:00
RF$328621377.2701/02/2018 00:00
RF$446070845.3701/03/2018 00:00
RF$357567501.3601/04/2018 00:00
RF$456199138.7601/05/2018 00:00
RF$421862533.3701/06/2018 00:00
RF$392615815.8201/07/2018 00:00
RF$465390953.8301/08/2018 00:00
RF$607816648.6201/09/2018 00:00
RF$541901652.9201/11/2018 00:00
RF$514735218.3401/12/2018 00:00

 

Thank you 

1 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

View solution in original post

22 REPLIES 22
Anonymous
Not applicable

Hi,

 

You can use SAMEPERIODLASTYEAR().

More Infor

Anonymous
Not applicable

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.

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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

Anonymous
Not applicable

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 )
            )
        )
    )
Anonymous
Not applicable

Sadly that comes back with the same answer as ;

 

Last Year = 
CALCULATE (
    [TotalYTD - Actual],
    SAMEPERIODLASTYEAR ( DIM_Date[Date] )
)
Anonymous
Not applicable

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.

Anonymous
Not applicable

Hi,

 

I have tried to copy your code but I am getting this error

 

image.png

Anonymous
Not applicable

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"))
)
Anonymous
Not applicable

arrr yes! will test now 🙂

Anonymous
Not applicable

image.png

I am getting this error i get when i add the =

Anonymous
Not applicable

@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

Anonymous
Not applicable

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:

DesiredResult.png

Are the left two columns the result that you want?

Anonymous
Not applicable

Hi @Anonymous

 

 image.png

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

Anonymous
Not applicable

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.

Anonymous
Not applicable

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;

image.png

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

Anonymous
Not applicable

Hi @v-juanli-msft

Really I want to see the below in the red.

image.png

 

From this, I would also work out the difference between them all to the actual ytd values.

 

Thanks

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.