Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
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 .
Best regards,
Dina Ye
Hi Dina,
Sorry for the confusion, Dina - my mistake.
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!
phil
Hi @philpringuer ,
Not sure if you'd like to show like this:
I added a new calculated column to replace the month with fiscal month: and then use the cumulative measures:
FiscalMonth = IF([FactMonth]>=7,[FactMonth]-6,[FactMonth]+6)
2016-2017Cumulative = CALCULATE(SUM('Table'[2016/2017]),FILTER(ALL('Table'),[FiscalMonth]<=MAX([FiscalMonth])))
2017-2018Cumulative = CALCULATE(SUM('Table'[2017/2018]),FILTER(ALL('Table'),[FiscalMonth]<=MAX([FiscalMonth])))
Best regards,
Dina Ye
You are also going to want to use a dedicated data table. You can use the columns from that table in your measures. You really do not want ever use FILTER over a fact table.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |