Sum on last available date of each month

[#1]

We are running a data warehouse which contains a measure date, a resource name and a workflow count. This data warehouse is updated every night, and we are cleaning up data older than a week, only keeping the data of mondays. This results in the following (example) table:

Meaure Date Resource Name   Workflow Count
17-2-2014    Resource A           10  
17-2-2014    Resource B           12
24-2-2014    Resource A           12
24-2-2014    Resource B           12
3-3-2014      Resource A           13
3-3-2014      Resource B           11
10-3-2014    Resource A           14
10-3-2014    Resource B           10
17-3-2014    Resource A           12
17-3-2014    Resource B           9
24-3-2014    Resource A           11
24-3-2014    Resource B           8
31-3-2014    Resource A           11
31-3-2014    Resource B           8
1-4-2014      Resource A           12
1-4-2014      Resource B           6
2-4-2014      Resource A           14
2-4-2014      Resource B           7
3-4-2014      Resource A           12
3-4-2014      Resource B           10
4-4-2014      Resource A           11
4-4-2014      Resource B           9
5-4-2014      Resource A           9
5-4-2014      Resource B           12
6-4-2014      Resource A           11
6-4-2014      Resource B           11

I want to build a graph on this table, only showing the sum of the workflow on the last available measure date of each month. Above table should result in:
24-2-2014: 24
31-3-2014: 19
6-4-2014: 22

Is this possible using set analysis, and which expression could I use?

Thanks in advance.

Het bericht is bewerkt door: Andreas Markus

Het bericht is bewerkt door: Andreas Markus

[its_anandrjs]

Please explain more on the final result it is not clear please clear it more.

and

Where is 25-2-2014: 24 this date come it is not in sample.

[#3]

Sorry, my mistake. I've edited my original post.

I need the sum of the workflow on the last available date of each month.

[Anonymous]

Take ,

[Meaure Date] as dimension

Expression>> sum({$<[Meaure Date]={$(=max([Meaure Date]))}>}[Workflow Count])

[Anonymous]

PFA,

[#6]

Sorry for the late response. I still prefer to do it via set analysis, but it still doesn't fully work. When I use sum({$<[Meaure Date]={$(=max([Meaure Date]))}>}[Workflow Count]), it says "Error: Error in set modifier ad hoc element list: ',' or ')' expected"

I can solve this as follows: "=Sum({$<[Date] = {'$(=Max([Date]))'}, [IsWorkflow] = {1}>}[Workflow Count])", but than it only displays the data from the last date of the last month. Not the last date per month.

Any idea how this can be solved?

[rustyfishbones]

You could try

AGGR(Sum({$<Date ={'$(=DATE(MAX(Date)))'}>}[Workflow Count]),Month)

[#8]

Thanks, but somehow it still only gives me the last date of the last month.

[rustyfishbones]

or maybe try

FIRSTSORTEDVALUE

[tresesco]

May be like attached sample;

Exp:

=FirstSortedValue(Aggr(Sum([Workflow Count]),[Measure Date]), -Aggr([Measure Date],[Measure Date]))