Assign a maximum value per index

[Kris48]

When the 'Index' in the 'Fact' table has a 'data from' later than today's date, then take the last 'value' from the 'Rev' table assigned to that index and multiply this value by the number of days that occur between 'Data from' and 'Data to'

Pbix https://drive.google.com/file/d/1BP1IknHOaf8PCym-sLHHkjYeN1g1S6um/view?usp=sharing 

[shafiz_p · Accepted solution]

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 * DaysBetween

Output I have :

Hope this helps!!

If this solved your problem, please accept it as a solution and kudos!!

Best Regards,

Shahariar Hafiz

[Kris48]

[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.

[Kris48]

Should I start a separate post for my logic? Will it be possible to solve my problem in this thread?

[Kris48]

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?

[Kris48]

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

[v-yilong-msft]

Hi @Kris48 ,

I tried to open your .pbix file, but I wasn't able to open it.

Just from your screenshot I don't fully know what data is in your table.

There are a lot of column names that do not appear in your DAX code.

Please provide the relevant example data if you can or use Dropbox for the .pbix file upload.

Best Regards

Yilong Zhou