Forum Discussion
Helper IRolling 12 Month Count within a single file, from a folder data source
Dispite trying to follow the various other solutions, nothing seems to work. I believe what I'm trying to do is a little unique compared to previous solutions, hence the new topic.
So I've got my report is pulling its data source from a Folder, in that folder I'm dropping a new data file each month, dated with a data date. My query is creating a Reporting Date (EOMONTH -1 of the data date). This combined table is called "Report"
In my report I have a calculated date table (DataDateFilter), that I'm using a the main filter for my report. This has a Dates and MonthYear Column. The dates list is everything between the earliest Reporting Date and the latest.
There's a relationship between 'DataDateFilter'[Date] and 'Report'[Reporting Date].
I've got a few other tables linked to DataDateFilter in the same fashion. The idea is the user can select a MonthYear in the slicer, and the Power BI report will change to reflect the reported data for that month. Like a history roll back function.
I've got a duplicate Date Table using 'Report'[Lodged Date]. The relationship for this one is 'Report'[Lodged Date] to 'AxisDateTable'[Date]. I'm using [MonthYear] on this table on all my axis.
What I've got so far is below.
What I need to acheive next, is selecting a date on that filter, needs to only display the 12 months prior to that date on the axis (select Aug 20, you get Jul 19 to Aug 20, and so on)
The bars are I've got right I think. Those are meant to be the counts for each catagory within that month.
The line needs to be the rolling 12 month count of all catagories up until that month (So Aug 20 should be like 101, Jul 20 should be 96, Jun 20 is 69), but within the file of a single data date only. Changing the DataDateFilter to Jul 20, should select all the files for Jul 20, and rolling count will be based on that file only. Selecting Aug 20 on the DataDateFilter, the first month on the axis will by Jul19. Jul19 will be like 75). I tried filtering away prior to the 12 month window for each data file, within the query it's, and that gets the axis right, but then my rolling count line starts at 0 for each period :S
Hopefully I'm making sense :S.
I'm abit green with Power BI, and this is doing my head in, because I need to achieve a similar result across like 4 reports I've got to build :S
Any help would be greatly appreciated.
Omg got it! Here's the formula for anyone else that comes across this.
CALCULATE(COUNTA('Report'[Notice Number]),DATESBETWEEN(AxisDateTable[Date],SAMEPERIODLASTYEAR(LASTDATE(AxisDateTable[Date])),LASTDATE(AxisDateTable[Date])))
7 Replies
- Greg_Deckler
Community Champion
wetscott - Sorry, having trouble following, can you post sample data as text and expected output?
This might help: https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499#M124
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
wetscottTried to follow a couple of those, but things just aren't quite lining up. I think there's a problem with the fact i'm trying to do counta on one date column, but only for entries within a single data file.
Here's my sample pbix.
https://app.box.com/s/r54rhft8nt7s5ksekidnisrvcwqz9wvf
Here's a same chart (did a link Painting :P)
https://app.box.com/s/btu286ghuun5nci80g97kk5bb0ovtxmc
Total This Month 12 Month Running Total Ending This Month Sep 19 0 47 Oct 19 5 43 Nov 19 14 57 Dec 19 5 59 Jan 20 0 59 Feb 20 11 70 Mar 20 6 72 Apr 20 5 72 May 20 2 69 Jun 20 4 69 Jul 20 40 96 Aug 20 10 102
- amitchandak
Super User
wetscott , as per what I get is you need rolling 12 months based on the selected date.
Best is relative date slicer if it fits in
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range
This is one solution that should work for rolling 12 with some modifications - https://www.youtube.com/watch?v=duMSovyosXE
Hi wetscott ,
Not sure if i understand you correctly, but you may check the below measure.
Measure = CALCULATE(SUM('Table'[Total This Month]),FILTER(ALL('Table'),FORMAT('Table'[yearmonth],"YYYYMM")>FORMAT(EDATE(SELECTEDVALUE('Table'[yearmonth]),-12),"YYYYMM")&&FORMAT('Table'[yearmonth],"YYYYMM")<=FORMAT(SELECTEDVALUE('Table'[yearmonth]),"YYYYMM")))Best Regards,
Jay
Best regards,
Jay
- wetscott
Unfortunately that Measure is getting Year to Date of what's displayed in the table only.
What I'm trying to indicate at each month though is the rolling Year to Date total at that month.
ie.
at Aug20, total Sept20 until Aug20
at Jul20, total Aug20 until Jul20
at Jun20, total Jul20 until Jun20
- wetscott
I'm still struggling with this one. I think I need some variation of this?
Count of Notice Number total for MonthYear =
CALCULATE(
COUNTA('Report'[Notice Number]),FILTER(ALLSELECTED('Report'),format('Report'[Lodged Date], "YYYYMM") < FORMAT(MAX('Report'[Lodged Date]), "YYYYMM")&&format('Report'[Lodged Date], "YYYYMM") >= format(EDATE('Report'[Lodged Date],-12), "YYYYMM")))But can't get it to display anything. And when it does, it's not even close.My thinking is my measure needs to look at the Report table while repecting the filter on the [DataDate] column set by 'DataDateFilter'[MonthYear].Then, for each month on my axis, it needs to count -12 months to current axis month.I'm just struggling to get anything that's rolling though. Everything comes out running total 0 to current :S- wetscott
Omg got it! Here's the formula for anyone else that comes across this.
CALCULATE(COUNTA('Report'[Notice Number]),DATESBETWEEN(AxisDateTable[Date],SAMEPERIODLASTYEAR(LASTDATE(AxisDateTable[Date])),LASTDATE(AxisDateTable[Date])))
