Rolling 12 Month Count within a single file, from a folder data source

[wetscott]

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.

[v-jayw-msft]

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]

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 · Accepted solution]

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])

)

)

[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

[amitchandak]

@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

[Greg_Deckler]

@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-TIT...

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.

[wetscott]

Tried 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