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,
It feels like I've researched the whole Internet without finding an answer to this. 🙂
I want to create a chart showing Moving annual total but 12 months in every sum:
January 2015 sums: Jan 15 - Dec 15
February 2015 sums: Feb 15 - Jan 16
March 2015 sums: Mar 15 - Feb 16
etc
All formulas I've found will only include <12 months before starting the rolling total which is not what I need:
Jan 15: Jan 15
Feb 15: Jan 15 + Feb 15
...
Dec 15: Jan - Dec 15
Jan 16: Feb 15 - Jan 16
It would also be nice to have it as an average (divided by 12).
The reason for this is that our data differs a lot depending on what month it is.
I have a calendar table and our transactions is on a day level.
Please let me know if you need any more information.
Thanks.
See my Quick Measure here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499
You should be able to modify this to go forward in time versus backwards. Also, you'd have to modify it to keep the day the same as mine currently use whole months. If I get time, I'll take a look at this further as these seem to be coming up a lot lately.
Hi Greg,
Thank you for your reply.
As I understand I will only get 1 value when using this measure. I would likte to have one bar per 12 months and compare the different sums to each other.
Lisa
No, if you use that measure correctly and place it in a bar chart with Months as the axis, it was designed to essentially get the "max" date of the current context (you just have to change the TODAY() to MAX() of your date field. See the other quick measure, Rolling Weeks for an example:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694
Hi Greg, I'm trying to change your week starting to month startin to practice my DAX skills but i get stuck when it comes to monthStart1. I created a monthStart2 to check whether my IF is correct and that one returns the correct values so I don't see why monthStart1 doesn't work.
Added my DAX below and attached an image of the result.
mMonthStarting =
//Get information about the current date
VAR myDate = MAX (dCalendar[Date])
VAR myMonthNo = MONTH (myDate)
VAR myYear = YEAR (myDate)
//Set min and max for Calendar table
VAR maxDate = DATE ( YEAR ( MAX (Sales[Posting_Date])); 12; 31)
VAR minDate = DATE ( YEAR ( MIN (Sales[Posting_Date])); 1; 1)
//Return date that matches the current year and Month No and is Month Day = 1 (first day of a month)
VAR monthStartDate = MINX ( FILTER ( dCalendar; YEAR ([Date]) = myYear && [Month Number] = myMonthNo && [Month Day] = 1); [Date] )
//If null; then it is not the first of a month
VAR monthStartDate1 = IF (ISBLANK (monthStartDate); monthStartDate; myDate)
//Control: If null; then it is not the first of a month
VAR monthStartDate2 = IF (ISBLANK (monthStartDate); "true"; "false")
//Return a nicely formatted month starting format "M# Month Start mm/dd/yyyy"
RETURN "M" & myMonthNo & " Month Starting " & monthStartDate1 & " (Control; " & monthStartDate2 & ")"
Thank you for your help, much appreciated.
https://community.powerbi.com/t5/Quick-Measures-Gallery/First-Last-Working-Days/m-p/391545
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |