Moving annual total 12 months in every sum

[LisaB]

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.

[Greg_Deckler]

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.

[LisaB]

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

[Greg_Deckler]

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

[LisaB]

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.

[Greg_Deckler]

https://community.powerbi.com/t5/Quick-Measures-Gallery/First-Last-Working-Days/m-p/391545