Skip to main content
cancel
Showing results for 
Search instead 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

Reply
NewbieJono
Post Patron
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
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
v-jianpeng-msft
Community Support
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

Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
rajendraongole1
Community Champion
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:

rajendraongole1_0-1717956172383.png

 

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!
Appreciate your Kudos!!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors