Measure for sum(value) for the latest Monday in selected the date range

[Kvitekvist]

Hello smart people,

I have a small challenge. I am trying to get the value in a measure to show sum(value) of the latest date in my table.

Let us say I need the value for all mondays, and the data for last monday. Mondays have "day of week" number as 2.

Only Mondays = CALCULATE(AVERAGE(Table1[Value]);Table1[Day of Week]=2)

This works great.

The next step is that I want to know the value of the most recent monday in my selected date range. This is where I am stuck. Any ideas on how to solve this? In SQL i would probably write something like:

select * from(

select date, sum(value) 

from table1

where day of week = 2

group by date) where date = max(date)

Link to sample file:

https://1drv.ms/u/s!AoE7VUyKvUkEuBS43wwMYK3MnN28

 

[Kvitekvist]

bump

[Kvitekvist · Accepted solution]

Solved it! A calculate around a calculate!

Last Monday =

CALCULATE(

CALCULATE(SUM(Table1[Value]);FILTER(ALLSELECTED(Table1[Date])

;Table1[Date]=MAX(Table1[Date])));Table1[Day of Week] = 2)

[luxpbi]

Hello, 

Why don't you try something like this?

CALCULATE(AVERAGE(Table1[Column1]);LASTDATE('Calendar'[Date]);'Calendar'[Week day num]=2)

[Kvitekvist]

Hello and thanks for the fast reply. I have tried something like this, but it only gives me the correct value if the last day of the selected data set = monday. But If i look at data from tuesday to another tuesday, I get blank. Even If I select only 2 dates (previous tuesday and previous monday) I also get blank because the latest value is a tuesday. I still want to have the monday's value show up.