Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
bump
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)
Hello,
Why don't you try something like this?
CALCULATE(AVERAGE(Table1[Column1]);LASTDATE('Calendar'[Date]);'Calendar'[Week day num]=2)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |