last available data based on month selection

[Varan_15]

Hi All,

I have calendar table and sales table so created relationship based on dates.

calendar date is used in slicer as month format , Now there is requirement if i select any month it should show that month last day of value  or if i select more month then sum of all those months last day value should show in card result.

EX:

sales table:

Date	Product	Sales	etc columns..
01/01/2024	101	60
02/01/2024	101	80
31/01/2024	101	90
02/02/2024	101	40
28/02/2024	101	26
04/03/2024	101	15
31/03/2024	101	78
01/04/2024	101	36
02/04/2024	101	5
03/04/2024	101	4

expected result like:

if i select slicer month (jan, mar, april) then sum of last date value should show. (172)

if i select only Jan then jan month last value should show (90)

thanks in advance.

Vara

[ryan_mayu · Accepted solution]

@Varan_15 

you can try to create a column in fact table

Column = if('Table'[Date]=maxx(FILTER('Table',year('Table'[Date])=year(EARLIER('Table'[Date]))&&month('Table'[Date])=month(EARLIER('Table'[Date]))),'Table'[Date]),"y")

then create the measure

Measure = sumx(FILTER('Table','Table'[Column]="y"),'Table'[Sales])

pls see the attachment below

[ryan_mayu · Accepted solution]

@Varan_15 

you can try to create a column in fact table

Column = if('Table'[Date]=maxx(FILTER('Table',year('Table'[Date])=year(EARLIER('Table'[Date]))&&month('Table'[Date])=month(EARLIER('Table'[Date]))),'Table'[Date]),"y")

then create the measure

Measure = sumx(FILTER('Table','Table'[Column]="y"),'Table'[Sales])

pls see the attachment below

[Varan_15]

@ryan_mayu ,

Thanks for your quick update it's working as expected.

[ryan_mayu · Accepted solution]

you are welcome