Calculatetable that filter by last day of each FY and product

[Anonymous]

Hi al

I've got a table were some KPIs are recorded as a snapshot of them to track the changes in time of it. something like this:

	Product	forescat	actual	FY	date
	P1	500	0	19	12/05/19
	P2	2000	100	19	12/05/19
	P1	500	10	19	01/05/20
	P2	2000	350	19	01/05/20
	P1	550	150	19	03/05/20
	P2	2250	500	19	03/05/20
	P1	550	250	20	04/05/20
	P2	2250	600	20	04/05/20
	P1	550	300	20	05/05/20
	P2	2250	650	20	05/05/20

I'd like to get a table that filters this one to show just the last values. I mean, the last values for each product for each FY. So the expected result would be:

Product	forescat	actual	FY	date
P1	550	150	19	03/05/20
P2	2250	500	19	03/05/20
P1	550	300	20	05/05/20
P2	2250	650	20	05/05/20

I'm trying this:

LastWeekly = 
    CALCULATETABLE(
        Weekly,
        Weekly[FY] IN DISTINCT(Weekly[FY],
        Weekly[Product] IN DISTINCT(Weekly[Product]),
        LASTDATE(Weekly[Date])
    )

but the last date for each FY it's not working and I got just the last date. I don't get any row from FY 19.

Appreciate your help

Thank you.

[v-lili6-msft]

hi @Anonymous 

Just try this formula as below:

New LastWeekly = 
    CALCULATETABLE(
        Weekly,
      FILTER(Weekly,Weekly[date]=CALCULATE(LASTDATE(Weekly[date]),FILTER(Weekly,Weekly[FY]=EARLIER(Weekly[FY]))))
)

Result:

Regards,

Lin

[Anonymous]

great!! both solution works fine!!

Thanks!!

Now I'm looking to be able to make a total sum of the columns, forecast for example to use a slicers and get the results by product and FY. I got key tables for both product and FY to filter from and realation between tables are done. I used SUMX but seems it's not working. Any clue?

[Anonymous · Accepted solution]

@Anonymous Here is my approach 

First create a measure in your original table 

Measure = 
VAR _maxdate = CALCULATE(MAX('Table'[date]),ALLEXCEPT('Table','Table'[Product],'Table'[FY]))
RETURN IF(MAX('Table'[date])=_maxdate,1)

Then create a calculated table from modeling tab

Table 2 = FILTER('Table',[Measure]=1)

 Let me know if have any question