cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

## Volume of previous fiscal year

Hello, what is the best way to dynamically pull back the total volume for the previous financial year (UK).

for example. today I would like to see the volume for last fiscal year 01/04/2023 to 31/03/2024

Thank you

1 ACCEPTED SOLUTION
Super User

Hi,

Create a Calendar Table with a relationship (Many to One and Single) from the Date column in your Fact Table to the Date column in the Calendar Table.  Write these measures

Total = sum(Data[Qty])

Total in previous FY = calculate([Total],datesbetween(Calendar[date],if(month(today())<=3,date(year(today())-2,4,1),date(year(today())-1,3,31),date(year(today())-1,4,1),date(year(today()),3,31))))

If this does not work, then try this

Total in previous FY = if(month(today())<=3,calculate([Total],datesbetween(Calendar[date],date(year(today())-2,4,1),date(year(today())-1,3,31))),calculate([Total],datesbetween(Calendar[date],date(year(today())-1,4,1),date(year(today()),3,31))))

Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3
Community Support

Your solutions is so great @Ashish_Mathur and @rajendraongole1

Hi, @NewbieJono

Has your current issue been resolved? I see that rajendraongole1 and Ashish_Mathur both provide a possible solution where you can mark a helpful reply as a solution so that other members of the community can quickly find an answer if they have the same doubts. Thank you for your cooperation.

Best Regards

Jianpeng Li

Super User

Hi,

Create a Calendar Table with a relationship (Many to One and Single) from the Date column in your Fact Table to the Date column in the Calendar Table.  Write these measures

Total = sum(Data[Qty])

Total in previous FY = calculate([Total],datesbetween(Calendar[date],if(month(today())<=3,date(year(today())-2,4,1),date(year(today())-1,3,31),date(year(today())-1,4,1),date(year(today()),3,31))))

If this does not work, then try this

Total in previous FY = if(month(today())<=3,calculate([Total],datesbetween(Calendar[date],date(year(today())-2,4,1),date(year(today())-1,3,31))),calculate([Total],datesbetween(Calendar[date],date(year(today())-1,4,1),date(year(today()),3,31))))

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Champion

Hi @NewbieJono - I hope you have a date table in your model. If not, create one using dax formulae and  also mark your Date table.

you can create a measure to calculate the volume for the previous financial year and here i am assuming Profit is metric field and Date colum referencing from DateTable1.

PreviousFYVolume =

VAR CurrentFYStart = DATE(YEAR(TODAY()), 4, 1)
VAR PreviousFYStart = EDATE(CurrentFYStart, -12)
VAR PreviousFYEnd = EDATE(CurrentFYStart, -1) - 1
RETURN
CALCULATE(
SUM(financials[Profit]),
DATESBETWEEN(DateTable1[Date], PreviousFYStart, PreviousFYEnd)
)

Image FYR:

To display the total volume for the previous financial year in Power BI.

Did I answer your question? Mark my post as a solution! This will help others on the forum!