Calculate SUM for last period

[carlochecchia]

Dear all,

Could you please help me solving one issue?

I have the following table where i have a few items, a store name  and the amount they are worth.

PERIOD	DATE	STORE	NETWORTH
201701	jan/17	A	100
201701	jan/17	B	200
201701	jan/17	A	300
201701	jan/17	C	100
201701	jan/17	D	100
201702	feb/17	A	200
201702	feb/17	C	300
201702	feb/17	A	100
201702	feb/17	C	200
201702	feb/17	A	300
201702	feb/17	D	300
201703	mar/17	D	100
201703	mar/17	B	100
201703	mar/17	A	100
201703	mar/17	C	200
201703	mar/17	A	300
201703	mar/17	B	100
201703	mar/17	A	200
201703	mar/17	C	300

	period
	201701	201702	201703
A	400	600	600
B	200		200
C	100	500	500
D	100	300	100
total  networth	800	1.400	1.400

I would like to report a table with the sum of the networth, per store, for the last period available (in this case - march 17 - 201703)

I tried to create a measure where  i would calculate the sum of the networth for the LAST PERIOD:

NetWorth.Sum.Last.Period = calculate( sum('Table'[NETWORTH]) ; lastdate('Table'[DATE] ))

Unfortunately, this is not working properly when I use the data slicers.

When set the slicer for the period feb 2017, it gives the wrong results:

STORE	201702_WRONG	201702_CORRECT
A	600	600
B	200
C	500	500
D	300	300
TOTAL	1.600	1.400

The reason for this error is because the formula LASTDATE  returns the last non Blank Value. Therefore it returns 200 ( for the B store  ) when it should return ZERO.

How I can fix this formula to get the amount only for the last period avaliable?

Thanks in advance

[v-jiascu-msft]

Hi @carlochecchia,

Could you please mark the proper answer as solution?

Best Regards!
Dale

[v-jiascu-msft · Accepted solution]

Hi @carlochecchia,

Can you share your pbix file please? I tested your scenario. It worked fine.

Best Regards,

Dale

[Interkoubess]

Hi @carlochecchia,

What about creating a calendar table and link it to your fact table and then creat a measure with time intelligence functions like previousmonth:

I created this measure and it seems to work:

NetWorth.Sum.Last.Period = calculate( sum('Table'[NETWORTH]) ; previousmonth('Calendar'[DATE] ))

Hope it helps...

Ninter