YoY Comparison totals - DAX Help

[sarah2_williams]

Hi all,

I am trying to do a year on year comparison on the number of deposits paid for courses starting in September. I have tried using the SAMEPERIODLASTYEAR function but that only gived me the total for 1st Jan 2019 - 9th August 2019, I want deposits paid for the starting in the september 19/20 academic year for all dates up until the last date a deposit was paid this year (e.g. someone could pay for a september 2019 course as far back as 2016)

The data table has the following columns - Creation Date (when deposit was paid) - Deposit Paid (1 = Y, 0 = N) - Academic Year (19/20, 20/21

Thanks

S

[v-lili6-msft]

hi  @sarah2_williams 

First, for SAMEPERIODLASTYEAR function is a time intelligence function, so you need a dim date table

https://radacad.com/do-you-need-a-date-dimension

and for your case, you need a Fiscal Calendar date table.

Then create a meausre as below:

YoY % = 
VAR _maxdate =
    MAX ( 'Table'[Created Date] )
RETURN
    VAR __PREV_YEAR =
        CALCULATE (
            SUM ( 'Table'[Deposit Paid] ),
            DATEADD ( 'Fiscal Calendar'[Date], -1, YEAR ),
            FILTER (
                ALL ( 'Fiscal Calendar' ),
                'Fiscal Calendar'[Date] <= EDATE ( _maxdate, -12 )
            )
        )
    RETURN
        DIVIDE ( SUM ( 'Table'[Deposit Paid] ) - __PREV_YEAR, __PREV_YEAR )

here is sample pbix file, please try it.

Regards,

Lin

[Greg_Deckler]

@sarah2_williams You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[sarah2_williams]

Thanks,

I have read through something similar to this.. but I still can't work out what I should be using. Basically I want a formula that does this - Sum the number of deposits paid where date is all dates to this date last year. 

I am no expert on DAX and find the Time and Date functions very difficult to get right.

Thanks

S

[richbenmintz]

Hi @sarah2_williams,

Please provide a sample table of data and the expected results, much easier to craft a measure that works if provided.

Thanks,

Richard

[sarah2_williams]

Created Date	Deposit Paid	Academic Year
01/11/2018	1	19/20
01/12/2018	0	19/20
01/01/2019	1	19/20
01/02/2019	1	19/20
01/03/2019	1	19/20
01/04/2019	0	19/20
01/05/2019	1	19/20
01/06/2019	0	19/20
01/07/2019	1	19/20
01/08/2019	0	19/20
07/08/2019	1	19/20
01/09/2019	1	19/20
01/10/2019	1	19/20
01/11/2019	0	20/21
01/12/2019	0	20/21
01/01/2020	0	20/21
01/02/2020	0	20/21
01/03/2020	1	20/21
01/04/2020	0	20/21
01/05/2020	1	20/21
01/06/2020	1	20/21
01/07/2020	0	20/21
01/08/2020	0	20/21
07/08/2020	1	20/21

From the above I would like to show that for 20/21 Academic Year 4 deposits have been paid up until 7th August 2020, and this would then be compared to the 7 deposits paid for the 19/20 academic year by the 7th August 2019, the data updates weekly on a Friday, so I need it to look for the latest date this year, to then hopefully use that date last year. The end goal is to then create the % change YoY which I am able to do once the 2 seperate totals are worked out.

Thanks all for the help

S

[richbenmintz]

Hi @sarah2_williams,

I have created a couple Helper Columns in your Dataset

Year and DateInt, makes the prior period calcs really simple, best practice would be to create a date table and join to your fact table, but given that you are dealing with a non standard calendar, I would likely add these columns to your date table and use the same calcs, just referencing the date table rather than the fact table

Current Year Cummulative Deposits = 
    CALCULATE(sum('Deposits'[Deposit Paid]), filter(ALL(Deposits), 'Deposits'[AcademicYearNumber] = max(Deposits[AcademicYearNumber]) && 'Deposits'[DateInt] <= MAX('Deposits'[DateInt])))

Prior Year Cummulative Deposits =  
    CALCULATE(sum('Deposits'[Deposit Paid]), filter(ALL(Deposits), 'Deposits'[AcademicYearNumber] = max(Deposits[AcademicYearNumber])-1 && 'Deposits'[DateInt] <= max('Deposits'[DateInt]) - 10000))

Data I used looks like this

Created DateDeposit PaidAcademic YearAcademicYearNumberDateInt

November 1, 2018	1	19/20	2019	20181101
December 1, 2018	0	19/20	2019	20181201
January 1, 2019	1	19/20	2019	20190101
February 1, 2019	1	19/20	2019	20190201
March 1, 2019	1	19/20	2019	20190301
April 4, 2019	0	19/20	2019	20190404
May 1, 2019	1	19/20	2019	20190501
June 1, 2019	0	19/20	2019	20190601
July 1, 2019	1	19/20	2019	20190701
August 1, 2019	0	19/20	2019	20190801
August 7, 2019	1	19/20	2019	20190807
September 1, 2019	1	19/20	2019	20190901
October 1, 2019	1	19/20	2019	20191001
November 1, 2019	0	20/21	2020	20191101
December 1, 2019	0	20/21	2020	20191201
January 1, 2020	0	20/21	2020	20200101
February 1, 2020	0	20/21	2020	20200201
March 1, 2020	1	20/21	2020	20200301
April 1, 2020	0	20/21	2020	20200401
May 1, 2020	1	20/21	2020	20200501
June 1, 2020	1	20/21	2020	20200601
July 1, 2020	0	20/21	2020	20200701
August 1, 2020	0	20/21	2020	20200801
August 7, 2020	1	20/21	2020	20200807

Hope this helps,

Richard 

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[sarah2_williams]

Thanks

I now have values but for the prior year they are all out by about 20.. can you explain to me how the prior year formula identifys the date it should look for in the previous year? and why are we using "-10000". Thanks again for the help.. just trying to make sure I understand properly 🙂

S

[richbenmintz]

Hi @sarah2_williams ,

you will notice in the data and the formula used for prior year, the Date column was formatted as an INT like 20200101, this allows you to subtract the int 10000 and get the prior year month value. 

It looks like in your formula you are subtracting 10000 days from a date column

[sarah2_williams]

thanks,

I have changed it to an INT and it is now closer, but still out.

on the source data.. If I use Filters to look at 19/10 academic year and filter on the Created Date "is before = 18/08/2020" I get a total of 1014. The Prior Year Cumulative Total formula comes out as 1027. I cannot replicate the 1027 total no matter what I do.. this is why I asked about the dates.

Thanks

[richbenmintz]

Hi @sarah2_williams,

Can you provide the data or the pbix file?

[richbenmintz]

Hi @sarah2_williams,

please review the .pbix at the following link, sample.pbix 

Hope this Helps,
Richard

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[sarah2_williams]

Hi @richbenmintz 

Here is what I am getting.. i'm not sure where I am going wrong.

Looking at source data, 1020 is correct for 18/8/19. 

https://1drv.ms/u/s!AgqFHfAlcE6ngzjXYTupPdRsKrid?e=cbluRK

Thanks

S

[richbenmintz]

Hi @sarah2_williams ,

1020 is the the cummulative total for the academic year 19/20, you are missing the current year cummulative total in the example you provide, when i checked the prior year cummulative total it matches the current year cummulative total on the the corresponding dates in the prior year.

the updated file can be found here, https://1drv.ms/u/s!AhCeuF2piSWMg6EPCOW1TqN2V036UQ?e=Ch2lW5 

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[sarah2_williams · Accepted solution]

Thanks, now I can see what you mean by showing this cumulative year. how can I rectify this in the attached image.

In its test file, the Standalone Card has the Accumulated Measure of the previous year, which shows the correct amount 982, this is correct to note that the maximum on date is 07/08/2020, and this is the total for the academic year 2019 through 07/08/2019. I copied the DAX exaclty but my stll card shows the wrong number.

I can't see why it doesn't work. Maybe I'll have to try something else.

Thank you

[richbenmintz]

Hi @sarah2_williams ,

The Card has no filters applied, meaning that the context for that visual has no Year or Date, if you click on one of the the Academic year rows in your matrix it will update the correct number.

Make sense?

[harshnathani]

Hi @sarah2_williams ,

Use solution from @v-lili6-msft . 

It makes use of Time Intelligence which is needed in your case.

Regards,

Harsh Nathani

[sarah2_williams]

Hi,

The current year cululative worked for me.. but the last year one did not.

This is what I used. The error on prior year is "The MAX function only accpepts a colum reference as an argument... but I did not get this error on current year, same colums are used in both.

Current Year Cummulative Deposits =

    CALCULATE(sum('ALL APPS'[Deposit Paid]), filter(ALL('ALL APPS'), 'ALL APPS'[AcademicYearNumber] = max('ALL APPS'[AcademicYearNumber]) && 'ALL APPS'[Created Date] <= MAX('ALL APPS'[Created Date])))

Prior Year Cummulative Deposits =

    CALCULATE(sum('ALL APPS'[Deposit Paid]), filter(ALL('ALL APPS'), 'ALL APPS'[AcademicYearNumber] = max('ALL APPS'[AcademicYearNumber]-1 && 'ALL APPS'[Created Date] <= MAX('ALL APPS'[Created Date]) - 10000)))

Any further help would be appreciated.

Thanks

Sarah

[harshnathani]

Hi @sarah2_williams ,

Prior Year Cummulative Deposits =
CALCULATE (
    SUM ( 'ALL APPS'[Deposit Paid] ),
    FILTER (
        ALL ( 'ALL APPS' ),
        'ALL APPS'[AcademicYearNumber]
            = MAX ( 'ALL APPS'[AcademicYearNumber] ) - 1
            && 'ALL APPS'[Created Date]
                <= MAX ( 'ALL APPS'[Created Date] ) - 10000
    )
)

You missed a ) after MAX function

Regards,

Harsh Nathani