cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GTPowerBIUser
Helper III
Helper III

Cumulative Totals by Company's Fiscal Year

Hello,

 

I'm trying to get cumulative totals using the following table:

 

Table Name: PS_SalesHistory

Columns: Sales, Effective Date, Fiscal Year

 

This calcuation is giving me cumulative total for all dates, but I need to break that up by the Fiscal Year column.

The fiscal year for my company is August 1 to July 31, so it doesn't follow the calendar year.

 

CALCULATE(
sum( PS_SalesHistory[Sales]),
FILTER(
ALLSELECTED(PS_SalesHistory),
PS_SalesHistory[Effective Date] <= MAX(PS_SalesHistory[Effective Date])
))
 
This is what it looks like right now:
2021-06-14_11-02-43.jpg
 
Each FY should have its own cumulative totals.
 
Is it possible to do this?
1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@GTPowerBIUser when working with dates,  as a best practice add a calendar/date dimension your model, follow my blog post to add this table Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

 

Create the following measure and use the Year from date dimension table.

 

CALCULATE(
sum( PS_SalesHistory[Sales]),
DATESYTD ( Calendar[Date], "07/31" )
)

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

@GTPowerBIUser not sure why, change cross filter to single, and if doesn't work, share the pbix file, remove sensitive information before sharing.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





GTPowerBIUser
Helper III
Helper III

@parry2k 

 

I have a date dimension table, however  its not working with that table. When I insert your calculation I get:

 

2021-06-14_11-39-21.jpg

 

CALCULATE(
sum( PS_SalesHistory[Sales]),
DATESYTD ( Dim_Date[Date], "07/31" )
)
 
I have a relationship between Dim_Date and PS_SalesHistory on Effective Date and Date:
Cardinality - Many to One
Cross Filter - Both
parry2k
Super User
Super User

@GTPowerBIUser when working with dates,  as a best practice add a calendar/date dimension your model, follow my blog post to add this table Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

 

Create the following measure and use the Year from date dimension table.

 

CALCULATE(
sum( PS_SalesHistory[Sales]),
DATESYTD ( Calendar[Date], "07/31" )
)

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!