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,
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
Solved! Go to 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.
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)
Proud to be a Super User!
you are welcome
Proud to be a Super User!
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.
This worked - thank you so much. Ros
You are welcome.
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
Proud to be a Super User!
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.
i created a measure instead of column
pls see if this is what you want
Proud to be a Super User!
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
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‚Ö%ødU½bŽ`ÎÐœH¦|$F‰!‘‚¹OÜM¿ÚèaÜÅFý2ÄkrÖ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è:³> O8¼¢Ï¾û4¾Fÿ ú¼¦+<†m? }\Ó8í-‡Á® œ5æFu0ß>Fj}-U,¬ãjݲÕøg—$ù0åz dBCÛü«mÆžŸãT±ñ‰8qŸžÕ µ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çøg3¦¤$²àò©¢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
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)
Proud to be a Super User!
Thank you so much for sending that.
Best wishes
Ros
you are welcome
Proud to be a Super User!
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |