Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dave_dingle
Frequent Visitor

Cumulative revenue issue

Hi, require some assistance around cumulative totals.  I'm a relative noob to Power BI but hopefully I've provided enough info.

 

The issue stems from the fact that our year start is April.  I've created a calendar table and i'm using a key measure for cumalative revenue.  The problem is that revenue for Jan, Feb, Mar is not displaying correctly, it should be added to the totals from December.

 

The key measure is as follows, I'm guessing this is where the problem may lie  

Cumalative Revenue YTD =
CALCULATE(
[Total Revenue TY],
CALCULATETABLE(
DATESYTD('Calendar'[Date]),
'Calendar'[DatesWithSales] = TRUE
)
)

 

graph.png

The month order is defined by my calendar table which has a fiscal month number and is working as expected.

 

Thanks in advance for any assistance you are able to provide.

 

dave

1 ACCEPTED SOLUTION

Hi  @dave_dingle ,

 

First create a calendar table as below:

calendar table = ADDCOLUMNS(CALENDAR("2020-1-1","2021-12-31"),"Month",FORMAT(''[Date],"MMM"))

Then create a relationship between the two tables :

vkellymsft_0-1627030870198.png

And  a measure as below:

Measure = TOTALYTD(SUM('Table'[Revenue]),'calendar table'[Date],ALL('calendar table'),"3/31")

And you will see:

vkellymsft_1-1627030895548.png

For the related .pbix file,pls see attached.

 

 

 

Best Regards,
Kelly

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

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

Create a Calendar Table with a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  In the Calendar Table, write calculated column formulas to extract Month Name and Month number.  To extract the Financial Year, write this calculated column formula

Financial Year = if(calendar[Month number]>=4,year(calendar[date])&"-"&year(calendar[date])+1,year(calendar[date])-1&"-"&year(calendar[date]))

Create another 2 column table (nae it as Month order) with month names and month order.  Create a relationship from the Month name column of the Calendar Table to the Month name column of the Month order table.  Via the RELATED() function, bring over the Month order from the Month Order table to the Calendar Table.  In the Calendar Table, sort the Month name by the Month order.  To your visual, drag Financial Year and Month name to the X-axis and write this measure:

Measure = calculate([total revenue],datesytd(calendar[date],"31/3"))

Hope this helps.

  


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
dave_dingle
Frequent Visitor

@v-kelly-msft 

Thank you so much for this, I can't tell you how much time I've spent investigating this issue.  I just needed your measure and used in conjunction with my existing calendar table and everything just worked.  Really appreciate you taking the time out to assist.

 

Dave

Hi  @dave_dingle,

 

Glad to help.

 

Best Regards,
Kelly

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

amitchandak
Super User
Super User

@dave_dingle , You need to create month order and

new column in date table

Month order = if(month([Date]) <=3,month([Date])+9, month([Date])-3)

 

Mark Month sort as Sort column for month and use

 

Sort Month.png

 

refer if needed

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

 

You can get correct FY calendar for any month from here

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

Many thanks for your prompt response.  I did try your solution but that gives me what I already have as I do have a month order column which as you can see does display them in the correct order.

 

Here is what I am expecting to see for Jan, Feb and Mar, the revenue should be increasing for each consecutive month (with Mar being my total revenue) whereas with my graph, Apr is totalling Jan, Feb, Mar and Apr.

 

graph1.png

Hope this makes sense.

 

Dave

Hi  @dave_dingle ,

 

So you mean that Jan should be Dec+Jan,Feb=Feb+Jan total,March=March+Feb+Jan total?And April=Jan+Feb+March?If I didnt understand correctly,pls correct me.

If possible,pls provide some dummy data with expected output for test.

 

Best Regards,
Kelly

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

Hi, thank you for your response and you are correct in your assumption of what I'm after.

As our year starts in April, this should be the first months sales £100 and therefore March should be the total for the year £1200 (based on us selling £100 every month).  I've included an example below of what I currently have and what I want.

 

Its the cumaltive totals that are off i.e. April is showing as £400 when it should be £100 so I believe something is awry with my 'cumalative total' measure.  The order of the months is as expected based upon the fiscal month column i'm using.

 

Hope this make things clearer.

 

powerbi.jpg

Dave

Hi  @dave_dingle ,

 

First create a calendar table as below:

calendar table = ADDCOLUMNS(CALENDAR("2020-1-1","2021-12-31"),"Month",FORMAT(''[Date],"MMM"))

Then create a relationship between the two tables :

vkellymsft_0-1627030870198.png

And  a measure as below:

Measure = TOTALYTD(SUM('Table'[Revenue]),'calendar table'[Date],ALL('calendar table'),"3/31")

And you will see:

vkellymsft_1-1627030895548.png

For the related .pbix file,pls see attached.

 

 

 

Best Regards,
Kelly

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.