I have years of fires data in a table named qryAllFiresSince1972Lite. Each fire has a unique [EventID] number.
Fires need to be summarised by Financial Year - i.e. starts in JUL, ends in JUN.
I can get monthly counts of fires per Financial Year.
I cannot get a cumulative count of fires by month (e.g. JUL = JUL; AUG = JUN + JUL; SEP = JUN + JUL + AUG), despite feeling like I've used the right 'pattern'. Obviously it's ME that's doing something wrong, but I can't work out what that is.
In the attached image, the top matrix is of fire counts by month, which works as expected.
* ROW headings as [FireStarted_MMM] ~ a calculated column as
I’m confused about your request : cumulative count of fires by month:
JUL = JUL; AUG = JUN + JUL; SEP = JUN + JUL + AUG (Financial Year - i.e. starts in JUL, ends in JUN.)
Could you please specify which year the June belongs to in your logic? if June belongs to the last fiscal year, it’s not reasonable to calculate it into this year. If it belongs to this year, then it points to the future month which probably doesn’t come currently. It’s not reasonable either.
Please confirm whether you’d like to get below results :
JUL = JUL; AUG = JUL + AUG; SEP = JUL + AUG + SEP…..JUN= JUL+AUG+SEP+….+MAY
Also, it’s preferred that share us your pbix file (upload to Onedrive and share your link here) which masking the confidential info. It’s easier for us to provide the further support .
Community Support Team _ Dina Ye If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I can confirm that I meant I'm hoping to achieve cumulative counts by month of Financial Year, where: JUL = JUL
AUG = JUL + AUG SEP = JUL + AUG + SEP
JUN = JUL + AUG + SEP + OCT + NOV + DEC + JAN + FEB + MAR + APR + MAY + JUN
July is the first month of the FY; so I'm hoping to start with July, and end with June.
The following chart (mocked up in Excel) is what I'm trying - unsuccessfully - to replicate in PBI...
I can't make your life easier by uploading a file to OneDrive - I don't have a personal OneDrive account, and the business one I have access to would be restricted. If that makes it too hard, then I'll understand you rescinding your offer of help!