Forum Discussion
Help with DAX Code, dynamically calculates the sum for the past 6 months.
Hi Steamedbunz
Those highlighted cells are blank because the max dates for those are only unitl October. Also, you cannot assign a value to a row that doesn't exist (eg., NSW and Nov-24). Instead use a separate dates table and create this measure:
Last 6 Months Tiles = CALCULATE ( SUM ( 'Tiles Order Data'[Tiles] ), DATESINPERIOD ( DatesTable[Date], MAX (DatesTable[Date] ), -6, MONTH ) )Notice that DATESINPERIOD is applied to DatesTable[Date] and not 'Tiles Order Data'[Date]
Please see the attached pbix.
Community ChampionSteamedbunz Hopefully this helps. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
You can just use an X aggregator with a FILTER and use EOMONTH or EDATE to dial you date back 6 months.
Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi Greg,
Thanks for your reply. I've provide some sample data below. Alternatively those numbers are summed in the last table (image) from the original post.
The DAX code can track the date back 6 months. However, for some States where the recent months won't have data. It should only calculate 3-5 months instead.
For Example if you look at 'NSW', it should sum Aug-Oct, and treat Nov-Jan as zero.
At the moment, because Nov-Jan has no data, it is calcualting May-Oct period as 'NSW' last 6 months, but all States should be calculated during the same period.
Date State Tiles
1/05/2024 QLD 94647
1/05/2024 SA 50097
1/05/2024 NSW 3373
1/05/2024 TAS 1871
1/05/2024 VIC 460222
1/05/2024 WA 3561
1/06/2024 NSW 6370
1/06/2024 QLD 70288
1/06/2024 SA 15131
1/06/2024 NSW 2745
1/06/2024 TAS 9346
1/06/2024 VIC 355937
1/06/2024 WA 14687
1/06/2024 QLD 8945
1/06/2024 VIC 49975
1/06/2024 WA 5775
1/07/2024 NSW 11812
1/07/2024 QLD 61967
1/07/2024 SA 64803
1/07/2024 TAS 32663
1/07/2024 VIC 924134
1/07/2024 WA 75328
1/07/2024 NSW 4027
1/07/2024 QLD 11731
1/07/2024 SA 11124
1/07/2024 VIC 118195
1/08/2024 NSW 5448
1/08/2024 QLD 96451
1/08/2024 SA 56466
1/08/2024 TAS 1821
1/08/2024 VIC 668783
1/08/2024 WA 32169
1/09/2024 QLD 11646
1/09/2024 SA 6733
1/09/2024 TAS 2679
1/09/2024 VIC 121388
1/09/2024 QLD 89871
1/09/2024 SA 35220
1/09/2024 NSW 11030
1/09/2024 TAS 5614
1/09/2024 VIC 513340
1/09/2024 WA 15987
1/10/2024 QLD 25045
1/10/2024 SA 25380
1/10/2024 NSW 6844
1/10/2024 TAS 10291
1/10/2024 VIC 663244
1/10/2024 WA 40284
1/10/2024 QLD 5063
1/10/2024 SA 7701
1/10/2024 VIC 135875
1/10/2024 WA 3632
1/11/2024 SA 41039
1/11/2024 TAS 4562
1/11/2024 VIC 538316
1/11/2024 WA 12083
1/11/2024 SA 1842
1/11/2024 TAS 4957
1/11/2024 VIC 64888
1/12/2024 SA 29245
1/12/2024 VIC 418946
1/12/2024 WA 11123
1/12/2024 SA 4538
1/12/2024 TAS 2417
1/12/2024 VIC 116477
1/01/2025 SA 34057
1/01/2025 VIC 415311
1/01/2025 WA 1481