Re: Balance of previous year

JPScotland · ‎08-15-2020

I have a table (PeriodBal) with weekly account balances from the start of the previous financial year to the present date (1. 5 years worth of data).

PerioidBal

ID	Period End Date	V Period Balance
1001	01/04/2018	550
1001	08/04/2018	562
1002	01/04/2018	998
1002	08/04/2018	305
and so on right up until today

I also have a Dates table that shows the finacial week (Start of April). The tables are joined by Period End Date = Date

Dates

Date	Year	Month	Week Number	Financial Week Number
09/08/2020	2020	August	31	19
10/08/2020	2020	August	31	19
11/08/2020	2020	August	31	20

I want to create a measure that gives me the balance of the previous year. I cant use SAMEPERIODLASTYEAR as the balances of each week fall on a different date i.e. Week 19 Ends on the 19/08 in 2020 but 11/08 in 2019

I have a measure (Most Recent Period Date) that gives me the most recent Period End Date = 09/08/2020. Using this I hope to pull the financial week from this year to look up last year.

Last Years Arrears Balance =
VAR WeekNo = LOOKUPVALUE(Dates[Financial Week Number],Dates[Date],[Most Recent Period Date])

RETURN
CALCULATE (
SUM(PeriodBal[V Period Balance]),
FILTER (Dates,Dates[Financial Week] = WeekNo
&& Dates[Year] = YEAR (TODAY() -1

)

I think the problem is the Lookup is bringing back multiple values so I had a read up on FIRSTNONBLANK but I cant seem to get it to work across tables.

I also feel I am over complicating the whole thing as I can show the data easily in a filtered table.

Any help would be very much apprecaited.

Cheers,

JP

JPScotland · ‎08-17-2020

Thank you both for replying. @harshnathani @amitchandak

@amitchandak - I thought about that way and it does work but I am not sure how accurate it will be when dealing with leap years etc?

I found this video which is similar to what I want to do (https://www.youtube.com/watch?v=g_3eLaKgeEQ). This issue was then that I didnt have FiscalYears in my calendar so I found this helpful site (https://goodly.co.in/financial-year-calculation-excel-and-power-bi/). The Fiscal Calendar is huge so I only setup a few years instead of the entire table. I then linked this to my Date table.

Balance 1 Year Ago = 
    VAR FiscalWeek = SELECTEDVALUE(FiscalCalendar[Fiscal Week])
    VAR FiscalYear = SELECTEDVALUE(FiscalCalendar[Fiscal Year])

RETURN
    CALCULATE (
            SUM (PeriodBal[V Period Balance]),
            FILTER(ALL(FiscalCalendar),
                    FiscalCalendar[Fiscal Week] = FiscalWeek
                    && FiscalCalendar[Fiscal Year] = FiscalYear -1)
    )

However, this is really only good in a table as far as I can see as it gives you the result for each row.

Thanks again.

JP

amitchandak · ‎08-16-2020

@JPScotland , same weekday is 364 days behind.

Week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Days))

Using week rank you can find last year same week (52 week behind)

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

refer my blogs

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Appreciate your Kudos.