Forum Discussion
Helper IIIYoY Comparison totals - DAX Help
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
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
18 Replies
- Greg_Deckler
Community Champion
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-TITHW/m-p/434008
sarah2_williamsThanks,
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
richbenmintzResident Rockstar
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
v-lili6-msftCommunity Support
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
