Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
Solved! Go to 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 :
And a measure as below:
Measure = TOTALYTD(SUM('Table'[Revenue]),'calendar table'[Date],ALL('calendar table'),"3/31")
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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.
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!
@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
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.
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.
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 :
And a measure as below:
Measure = TOTALYTD(SUM('Table'[Revenue]),'calendar table'[Date],ALL('calendar table'),"3/31")
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |