Collecting the last 12 months sales for each month using powerbi table

[Ros-ec]

Hi,

I did a post earlier, but can't seem to find it to connect to it, or edit it. Therefore apologies for another one, but it is a different question on the same line! I am new to PowerBI having done a course and also familiar with Excel for years.

I am tring to collate the 12 months sales at the end of each month. In Excel I did a turnover column:

=SUMIFS(G:G,D:D,"<="&EOMONTH(D2,0),D:D,">"&EOMONTH(D2,-12),I:I,"="&I2)

Where:

G=sales amount

D= sales date

I = client identifier

And that worked to give me the sales per client for the last 12 months for each month. 12 months sales from August 2022, 12 months sales from July 2022, 12 months sales from June 2022 and so on

In PowerBI I have the same data fields pulled through from Excel, and added a measure to try and do a similar thing - but its not working:

Turnover Test = calculate(sum('Sales Data'[Amount]),'Sales Data'[Date]>=EOMONTH('Sales Data'[Date],-12) &&'Sales Data'[Date]<eomonth('Sales Data'[Date],0))

Any help welcomed ... and thank you.

Best wishes Ros

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table and write calculated column formulas to Extract Year, Month name and Month number.  Sort the Month name by the Month number column.  Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of your Calendar Table.  To your visua, drag Year and Month name from the Calendar Table.  Write these measures:

Total = sum('Sales Data'[Amount])

Running total in past 12 months = calculate([total],datesbetween(calendar[date],edate(min(calendar[date]),-11),max(calendar[date])))

Hope this helps.

[ryan_mayu · Accepted solution]

@Ros-ec 

the measure is 

Measure = 
VAR _max=max('date'[Date])
VAR _min=EDATE(_max,-12)+1
return  CALCULATE(sum('Table'[value]),all('date'),'date'[Date]>=_min&&'date'[Date]<=_max)

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table and write calculated column formulas to Extract Year, Month name and Month number.  Sort the Month name by the Month number column.  Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of your Calendar Table.  To your visua, drag Year and Month name from the Calendar Table.  Write these measures:

Total = sum('Sales Data'[Amount])

Running total in past 12 months = calculate([total],datesbetween(calendar[date],edate(min(calendar[date]),-11),max(calendar[date])))

Hope this helps.

[Ros-ec]

This worked  - thank you so much. Ros

[Ashish_Mathur]

You are welcome.

[ryan_mayu]

@Ros-ec 

you can try this to create a column

Column = sumx(FILTER('Table','Table'[client ]=EARLIER('Table'[client ])&&'Table'[date]<=EOMONTH(EARLIER('Table'[date]),0)&&'Table'[date]>=EOMONTH(EARLIER('Table'[date]),-12)),'Table'[value])

pls see the attachment below

[Ros-ec]

Hi Ryan_mayu

Thank you for coming back to me, that was really helpful. The column formula now shows as:

Turnover Test 2 = sumx(filter('Sales Data','Sales Data'[ClientId]=EARLIER('Sales Data'[ClientId])&&'Sales Data'[Date]<=EOMONTH(EARLIER('Sales Data'[Date]),0)&&'Sales Data'[Date]>=eomonth(earlier('Sales Data'[Date]),-13)),'Sales Data'[Amount])

The visual now shows the columns:

Client, year, period for testing. 

I did try it Year, period and client too.

The good news is, when the client has an invoice in the month it sums the previous 12 months for that month. For example, if they have an invoice in month 6 and month 12 we get the 12 months back from each of those months.

The not so good news is when the client does not have an invoice in the month, then the client does not show. For example, month 7 - but there were invoices in month 6 and 12 which is part of the 12 month turnover figure so needs to be included.

Any thoughts and help most welcome.

Thanks

Ros

P.S I had another query last night which Jianbo Li kindly responded to. I can't link the two queries, so have replied to each separately.

[ryan_mayu]

@Ros-ec 

i created a measure instead of column

pls see if this is what you want

[Ros-ec]

Hi ryan_mayu

Thank you for coming back to me, appreciate your thoughts and help. I can't download the last12-v2.pbix - not srue what I am doing wrong. Would it be possible to let me know what the measure is? Thank you Ros

PREVIEW

[Ros-ec]

Hi Ryan_mayu

Thank you for coming back to me. I can't seem to download the attached? When I try to open it, its a text file and all I can see is:

"PK  xG/U—Œ±¦    Version ¢ (  3dÐc0B PK  xG/UÂc×Hî *   [Content_Types].xml ¢ (  ‘MNÃ0…¯byßNè¡*I%ZvEìGö$uÿÈžTÍÙXp$®€Kv¨•
Kß7ïÍÌ×Çg¹8ÚN(&ã]%o¦…ä”×Ƶ•ì¹™ÜÉE]¾ ’ÈR—*¹es€¤¶d1M} —-r~ƪ=¶³¢¸å“ã ŸzȺ\Qƒ}Çâá˜Ë£í.y'Årž¼*)Ôås&ñ‚‘ŸÐæ:¼Y¯•¯¶í#¾çk¡W
>2ü ÚsÞZú¥Ç:£sfç¼À¯‰Q#ãù ©>îÓgr\ÞPö\{MÝ9 ~._PK  xG/U~¤Ý>
š  DiagramLayout ¢ (  SMOÂ@œŸb<SŠõfüˆÆ‹¢㡲‹%©-¡E „ÿî¼é6A0ÁzØìÛ7oæÍÛnW8Ä<¦(1FœçK®Žµ"ÆG\Žh‚Ö%ød­U½bŽ`ÎÐœH¦|$F‰!‘‚¹OÜM¿ÚèaÜÅFý2ÄkrÖ9úÚì”17d—î*Œl_„éTy§žÓ™·Z§F1e<æ´){Y®‡SĪœ3ï˜M•ÑÇIèUoui<ì÷ÝÇÎxÓqÿNjuS/ú‡û·àÁ¾¸¯ô=¨ÑÞ`\ÔôZ°òYï`眮m¢WòHd‰;ÆžuŽü
y¶&bŒÄ/‰6³¥D渥¯J¯ïžH"§Syß奚ÔÿÒíE÷“ã:¼Í„}å¶TÚùK1=¿`¥ª›¾[1¦9c¶êÀ]ãPK  xG/U†}°€E <%  Report/Layout ¢ (  íZ[SÛFÞŸ’á%/j9HÞJÚÌ$àÆ”L'âÁøn}+–M‰Çÿ½ç|»«=»’%Ù@†¶ŒÇ¶.gÏùöÜw¥¥ÚQÕ¥ß7ª¦~ ÿÕS35Qs:êÐqSµéÿOú½Â¦üª–¥”LÇTc:™óÕR×tÎ#»ês0žy3‚DÝ©©QÇ•
]ë)ݧªÊ)qOH†–öŽÎfDsFWz@1S?ª#õ;QG„å7úÔ®ú8ÆF¾8¤ÞQ+u
º.áší¥b>Lߧó!d®R:>ëª
dÈ9øš÷å²v¦4â†F¶<;Bú”¤µI'Þئ±Áàeê>Q¡Åƒ`‡0\˜»,…ùÞ!Z@ÆW ;¡c¦s|ôLþÆqDšj>÷Ô
tU§£}X§Fÿ5ºú|ïõ!ÝÛ¥ßè÷èÌ?Þõ7è:³> O8¼¢Ï¾û4¾Fÿ ú¼¦+<†m? }\Ó8í-‡Á® œ5æFu0ß>Fj}-U,¬ãjݲÕøg—$ù0åz dBCÛü«mÆžŸãT±ñ‰8qŸžÕ µlb+†Þã 4C÷ñڏ¡³A\Ù
q`‡¸¢%lň#ö¼+DÛ¹ðL©é±g&C8KMéî‚t˜Ð=·Î*Sòé å?Z%÷Ïtí6c±¿HÇÃb¶/äu‘z4Ã;úå¼72Qt-䶍¼EŠ5!Ê9rˆ;Gߐ®¦rùn–»$cH2zþbCéçÐçùsÙŸ0û2½–¼WžÞ|µÅ~M¹Jퟛ¬#£ nx¼Q€î$«2
¡‹
¤fmæè|?ª
÷:þI)ÿ<ÏËJ°šçÌ?4éÏMz„ÔÒ1å„©©Í³œŒÑWÚ,{7ÔÚ*µXÚžÂþìòý܎˳Gq„8]Ïù&•ç›ªõs-ŽF7Ól´<¹æEq•¹G—®ro2¤v=Ê)<€d¦·ÙÇe¨•ªÒ߸,%+ù2ƈ8 E˜ÙZèÛ&†;"'ýŸm›^–5ås’:ÎdÏ™ì9“ý?2Ù;ô§]Ó¥J¼M丼ÂÏ?ã)
³P³ 'ýJñå¢kN(uåzâÈçW‘`|h$ɨ— ©{hleëv‰Þq•¨,Õy3Ö6¯„•óuûÅ؃{vyýó§y(Â*iUQOgð©jü,Ǽ|Š‹´†uÍ\ψ¶ íö±2¥MËË[§œb߃.BïW-µ’UETØ÷×3»ä­XO|H÷mŠð<Ì*jßu9EïWt0Vó’{3¿®ÚÕ©“ÊÙ†ýÁú…Ÿ
×US½«–²²º¶mUË«ú62ßïm{òkA(ûN¿›V¥<¤uìRn€&[
¶«Ûëí"¥’¯ïI0K=¢WRs§/)ïMïӍ­„í’ ÷|z³ö;æm»²õs®jéÇêËjÖy}ÖÊË’rŸï¼m¬†±àúŒ èÂûÙÈ•õI"õëœïeŒFV>}×­Sׯ³²¨Lfto™a]/“X/”Ö1·_ëjT¨õü§ÕªT¿ ?†5tÛŠvxK^ç"5Å¥‰_-í
˜­iïxèqstšŒwHºëÔ¾Y­Ëèþ/åî2XWÏ‹,]¥È·s^Wøý¬=K?|½*³óºNé~1_¥Ë÷„ì
è)Eý¿¹Ž»çèyOZÝšXúË)¹§àº†Ï„#óT1ïÉï
:þ"¹Œq¡²«uþåç«uâá|&Iß+ÐQi«]Vß—*|AÞÍ>Sß:p2‹Ñzè|or“Ïüüw>¯´Ï•mÓ…´áÁ õTÛïÙÛ'àÚãç Ô;ì-újíðÞúVËE;å¬Ïcå~ÕÒDß=¡ï­‡±‰•ž‰½_Òƒ¤°²šâYrÕ?ÂnÿïUHŽ6fóç©q´`‹a?R?Ù÷*~AÔèܐÇSgýfˆín?ÑyWT½¥kÒÙ ò>Â#ù½—ì^B˜ã&„Ûö…¾žýw[ôÛãM½¼0ÈäÖEz§¤oáWÄÇ_w/×濏j`¬¾àò¶‹—fïî¥ÈD6Ù|#ßÌ°ÙfDßoПË<œEþ
PK  xG/UÔÎC¤ P
  Settings ¢ (  …=Â0†Ÿß’Y¤U'7EG?p׶h±Ö’¤ƒ”þwßêRDŽä.wϽ¹¤Ãp¦Àâ(yQë¼dÎDþ |£œÅsTìe¥ˆ›Ø@uô‘ÛÓF…R»ûAN¼¥V°‰õ‚,2áÆ­Ö…+•²yä½Ô‚æ0E¥ú@;îŠv<¿ÓýShU_‹Èxh&«®ÉYžJ“…×»Qçøg3¦¤$²àò©¢D•^ö
PK  xG/U­ßWn À   Metadata ¢ (  «fPbcHe(b(fÈdÈgÈò­Lt€´#C)C PÌ(›Êd§2¤0
É0¢¾˜!È* Ò Ñ±`½¨zÜ€¼|†\°
\W
W£bH¼HBt1 ±!ƒ
ùµ PK  xG/U*4é ã ?  Report/StaticResources/SharedResources/BaseThemes/CY21SU11.json ¢ (  ÕXÛnÛ8ý•@ݧE dI¶ó&ÉÖ¶@¶Ô)‚EÑJbl”(PT. üï;CÊ9NêAƒä!‘s93s8¢üàT´dΙ“üç¹³¯®ëœ:Õ4‘BªÆ9ûæ|pÝÑ$Maýƒëyƒñ¥i&ƒ!JaLÈ06kÄ÷#³"

Thats just the top part of it. I am sorry - could you help?

Thank you.

Best wishes

Ros

[ryan_mayu · Accepted solution]

@Ros-ec 

the measure is 

Measure = 
VAR _max=max('date'[Date])
VAR _min=EDATE(_max,-12)+1
return  CALCULATE(sum('Table'[value]),all('date'),'date'[Date]>=_min&&'date'[Date]<=_max)

[Ros-ec]

Thank you so much for sending that.

Best wishes

Ros

[ryan_mayu · Accepted solution]

you are welcome