Sum values for last date in selection, grouped by store

[Anonymous]

Hi,

I need your help to sum the values for the last date in the selected months, at the level of each store.

1. I have a month slicer - where I have selected 3 months - Aug, Sep, Oct

Below is the table based on filters:

store_name	session_date	task_name	Total
STORE 1	13-Sep	MS	15
STORE 1	11-Oct	MS	75
STORE 2	31-Aug	MS	47
STORE 2	21-Sep	MS	69
STORE 2	05-Oct	MS	42
			248

From the above table I need to extract only the last session for each store, with the total, like below.

store_name	session_date	task_name	Total
STORE 1	11-Oct	MS	75
STORE 2	05-Oct	MS	42

So far I have:

1. A measure to calculate the MaxVisit: 

MaxVisit =max(ExportData[session_date])

2. A measure to get the last date facings:

LastDateFacings =

VAR CurrentName = SELECTEDVALUE(ExportData[store_name])

VAR LastDatewithData = LASTNONBLANK(ExportData[session_date],

                                                                  SUM(ExportData[number_of_facings]))

RETURN

          CALCULATE(

                            SUM(ExportData[number_of_facings]),LastDatewithData,ExportData[store_name]=CurrentName)

Somehow it's working, it does filter, but only If in the table I have the store name on rows, otherwise it displays nothing.

[AlexisOlson · Accepted solution]

Your measure won't work when there are multiple stores in the local filter context because SELECTEDVALUE(ExportData[store_name]) will return a blank when there isn't a single value.

One way around this is to iterate over each store individually like this:

SUMX ( VALUES ( ExportData[store_name] ), [LastDateFacings] )