Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there,
I need to create a measure that calculates the sum of total sales from the latest month only for each site. See image below.
I should get a total of 12, as it should SUM 4,4,4 from July, for each of Site a, b and c.
How can I do this?
Thanks
Solved! Go to Solution.
Here you go,
meaasure = VAR MAxxx = MAX(Table1[Month Number ]) RETURN CALCULATE(SUM(Table1[Sales]), Table1[Month Number ] = MAxxx)
This will work based on months.
if you add other years, the same months of other years will also be taken into account! so watch out.
Hi there,
What does your "Date" value look like in the original data? is it text or a date value?
Calculate(Sum(Sales); LastDate(Month))
What do you want your output to look like? just a card?
Hi,
The date value is just a text value of the month name only.
Yes just a card figure would be fine thanks
Could you be so kind to copy that table in here? Instead of a screenshot ? 🙂
Please see below!
Sales | Location | Month Number | Month |
1 | Site A | 1 | January |
1 | Site A | 2 | February |
2 | Site A | 3 | March |
3 | Site A | 4 | April |
3 | Site A | 5 | May |
3 | Site A | 6 | June |
4 | Site A | 7 | July |
1 | Site B | 1 | January |
1 | Site B | 2 | February |
2 | Site B | 3 | March |
3 | Site B | 4 | April |
3 | Site B | 5 | May |
3 | Site B | 6 | June |
4 | Site B | 7 | July |
1 | Site C | 1 | January |
1 | Site C | 2 | February |
2 | Site C | 3 | March |
3 | Site C | 4 | April |
3 | Site C | 5 | May |
3 | Site C | 6 | June |
4 | Site C | 7 | July |
Here you go,
meaasure = VAR MAxxx = MAX(Table1[Month Number ]) RETURN CALCULATE(SUM(Table1[Sales]), Table1[Month Number ] = MAxxx)
This will work based on months.
if you add other years, the same months of other years will also be taken into account! so watch out.
Brilliant thank you for your help!
Thanks very much that does work! What do we do in the event when other years are added on, do we just add a filter?
meaasure = VAR MAxM = MAX(Table1[Month Number ])
VAR MAxY = MAX(Table1[Year Number ]) RETURN CALCULATE(SUM(Table1[Sales]), Table1[Month Number ] = MAxM; Table1[Year Number ] = MAxY )
This is a good workaround, but not an ideal way to filter your data. Ideally you would work with a data value.
But this should work 🙂
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |