Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wetscott
Helper I
Helper I

Rolling 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.

Chart.PNG

 

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

View solution in original post

7 REPLIES 7
v-jayw-msft
Community Support
Community Support

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

1.PNG

 

Best Regards,

Jay

 

Best regards,

Jay 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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

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

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
Super User
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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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 Month12 Month Running Total Ending This Month
Sep 19047
Oct 19543
Nov 191457
Dec 19559
Jan 20059
Feb 201170
Mar 20672
Apr 20572
May 20269
Jun 20469
Jul 204096
Aug 2010102

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.