Forum Discussion
Assign a maximum value per index
Hi Kris48 Considering you want to achieve, If data from is later than today's date , then take the last value from the rev table assigned to that index and multiply by dates between. Try revise your measure by the code below:
Value_measure = VAR CurrentDate = TODAY() VAR FilteredFact = FILTER( 'Fact', 'Fact'[Data from] > CurrentDate ) VAR LastRevValue = CALCULATE( LASTNONBLANKVALUE('Rev'[Date], SUM('Rev'[Value])), TREATAS(VALUES('Fact'[INDEX]), 'Rev'[INDEX]) ) VAR DaysBetween = SUMX( FilteredFact, DATEDIFF('Fact'[Data from], 'Fact'[Data to], DAY)+1 ) RETURN LastRevValue * DaysBetweenOutput I have :
Hope this helps!!
If this solved your problem, please accept it as a solution and kudos!!
Best Regards,
Shahariar Hafiz
Super UserHi Kris48 Considering you want to achieve, If data from is later than today's date , then take the last value from the rev table assigned to that index and multiply by dates between. Try revise your measure by the code below:
Value_measure =
VAR CurrentDate = TODAY()
VAR FilteredFact =
FILTER(
'Fact',
'Fact'[Data from] > CurrentDate
)
VAR LastRevValue =
CALCULATE(
LASTNONBLANKVALUE('Rev'[Date], SUM('Rev'[Value])),
TREATAS(VALUES('Fact'[INDEX]), 'Rev'[INDEX])
)
VAR DaysBetween =
SUMX(
FilteredFact,
DATEDIFF('Fact'[Data from], 'Fact'[Data to], DAY)+1
)
RETURN
LastRevValue * DaysBetweenOutput I have :
Hope this helps!!
If this solved your problem, please accept it as a solution and kudos!!
Best Regards,
Shahariar Hafiz
- shafiz_p
You have not mentioned this calendar table in your post. However, how you are defining dates flaged as T. "Date From" is greater than current date, logic clear to me but How I would you go to another date table to find flag T. That means comparison criteria.
Do you want to count days between "Date From" and "Date To" except those dates flaged as T in calendar table?
Please redefine you complete logic.Yes, I'm sorry I didn't include this in the logic earlier. So the logic is as before. Your dax works, but I would like to add logic to exclude days that have the "T" flag on the calendar
- shafiz_p
Do you want to count day between "Date from' and "Date to" excluding the Date Flagged as T. Meaning how you want to consider? What the logic behind this?
Should I start a separate post for my logic? Will it be possible to solve my problem in this thread?