Display variable from the loaded file of the latest date

[KaisGarci]

Hello everyone, 

I am loading two excel files based on their date. The two latest files are loaded. 

I am trying to display sales variable which only belongs to the latest file. For example, I am loading two files for July and August and I want to display only the sales of August. 

Can anyone please help me ? 

Thank you

[Kushal_Chawda]

If you have Date field then you can create MonthYear field like below

LOAD *,

           date(monthstart(Date),'MMM-YYYY') as MonthYear

FROM Files;

Then you can use below expression

Sum({<MonthYear= {"$(=date(max(MonthYear),'MMM-YYYY'))"}>}Sales)

[KaisGarci]

Thank you @Kushal_Chawda for responding. But it gave me 0. 

[KaisGarci]

sum(N*if(Date_de_Situation=aggr(nodistinct max(Date_de_Situation), N),1,0)), I tried this expression, I got the right numbers but I have Something like this 

and I want to hide the column of the date 10/07/2020, when I put supress zero values, It will supress the 0's of all the table and I don't want that

[Kushal_Chawda · Accepted solution]

try below in calculated dimension instead

aggr(if(Date_de_Situation=aggr(nodistinct max(Date_de_Situation),Date_de_Situation),Date_de_Situation)

check suppress when value is null option

then simply use below expression

Sum(N)

[mikaelsc]

add a field "filename()" or "filebasename()" in your load script... and use that in a filter, or even in a set analysis.