Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I have a table as shown below that I would like to run some rolling months from.
I am fairly new to Power BI and Dax so struggling a little. Ideally I would like to display a graph that has a time filter, that will show the total number for the selected time period.
i.e. The WorkingDayActual_C would sum the numbers for the appropriate time span.
Am I able to do this just using this table? Do I need a date table to be linked and if so how would the formula work for this?
Any help would be very much appreciated.
Solved! Go to Solution.
Hi,
Unfortunately this website is blocked by our internbet security, so I am unable to view.
However, thanks to Bhavesh Patel, I have managed to understand the process more and seem to have found my solution:
NEWTEST_C = CALCULATE([TotalWorkingDaysActual_M],
DATESINPERIOD('KPI_ABS'[Month_Year],
LASTDATE('KPI_ABS'[Month_Year]),-11,MONTH
))
This gives me the result I need each month.
Thank you Bhavesh for your patience and help.
Kind Regards
Rich.
Hi There,
Date Table has a specific role in DAX Time intelligence calculations as It contains unique records of all the dates which is neccessary for the correct working of the time intelligence functions in DAX.
For creating a rolling 12 month average, there are variety of different ways you can deploy DAX Calculation.
Thanks & Regards,
Bhavesh
Hope this would clarify your understanding.
Thanks & Regards,
Bhavesh
It is this section I seem to struggle resolving:
Date[DateKey],
Rolling Average 12 Months = CALCULATE (
SUM('KPI_ABS'[WorkingDaysActual_C],
DATESBETWEEN (
Date[Month_Year],
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Date[DateKey] ) ) ),
LASTDATE ( Date[DateKey] )
)
)
I also see samples that start with Calendar, I feel stupid as I just can't work this one, I seem to hit a brick wall in my head!
Hi There,
'Date' is a Datetable and DateKey is the Date Column of your Date Table. in
'Date'[DateKey]
Be Cool. Just be with me. This will sort out soon.
Regards,
Bhavesh
Hi,
I have selected my Date Ranges table and date, but still get errors:
Hi There,
Please write two measures shown in the screen shot. It is tested and working fine.
Thanks & Regards,
Bhavesh
I have written the following two measures:
TotalWorkingDaysActual_M = SUM(KPI_ABS[WorkingDaysActual_C])
Rolling Average 12 Months_M = CALCULATE (
[TotalWorkingDaysActual_M],
DATESBETWEEN(
DATE_RANGES[Date],
NEXTDAY( SAMEPERIODLASTYEAR ( LASTDATE ( DATE_RANGES[Date] ))),
LASTDATE ( DATE_RANGES[Date] )
)
)
I have then created a graph and a time filter using the Date_Ranges[Date] for consistency. But my graph is still showing the over all total for each month.
Any test I do by turning measures to columns just show a value of 674 for each month?
Hi ,
i need simillar help on my data
i have colums like
Period divsion segment net
201501 a 1 12332
201502 b 2 21233
.. ... .... ......
201812 a 2 312333
I need to calcuate avg net value (from 201701 to 201712) for each division and segment by skipping recent 12 periods say (201801 to 201812)
your help would be appreciated.
Thanks
A calendar table is necessary if we’d like to use the Time Intelligence Functions. Please take a look at the .pbix file provided by BhaveshPatel. It seems to work well. In case you still have problem with it, please post back.
Best Regards,
Herbert
Hi,
Unfortunately this website is blocked by our internbet security, so I am unable to view.
However, thanks to Bhavesh Patel, I have managed to understand the process more and seem to have found my solution:
NEWTEST_C = CALCULATE([TotalWorkingDaysActual_M],
DATESINPERIOD('KPI_ABS'[Month_Year],
LASTDATE('KPI_ABS'[Month_Year]),-11,MONTH
))
This gives me the result I need each month.
Thank you Bhavesh for your patience and help.
Kind Regards
Rich.
Hi Rich,
Could you please elaborate on the Month-year field that you have used.
Additionally would like to know,if you are using Measure or Column for Rolling period Calculation.
Awaiting your response
Kind Regards,
Vaishnavi
Thank you Sir. Happy to hear that you found the solution.
We are a great community of PowerBI.
Thanks & Regards,
Bhavesh
Hi @RichWyeth
Please see the attached sample file.
https://drive.google.com/file/d/0B5-C_3XrFPdOX2pfVVNrSEVCeHM/view?usp=sharing
Thanks & Regards,
Bhavesh
Hi,
I found the error, a missing bracket.
So I now have a graph that gives me the total. But it is giving me the total for the whole period for each month, rather than a rolling total.
i.e. each month is showing a total 674.
Hi,
That works for me if I just want to display the result as a number, but if I put it into a graph, it is showing just the monthly total for each month rather than calculating the 12m rolling period each month.
My apologies I added the 12 month rolling comment after I posted the message.
Can you suggest a solution for this scenario?
Apologies, what I should have also stated, was that I would like to achieve a Rolling 12 month period.
I wasn't sure if I could do it using my Rolling Month column or not?
I have looked at some of the options available to me , but either can't quite get it to work or simply can't get things to work with my setup.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
59 | |
58 |