topic Re: Assign a maximum value per index in DAX Commands and Tips

Assign a maximum value per index

Kris48 — Sun, 03 Nov 2024 19:12:13 GMT

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

Re: Assign a maximum value per index

Anonymous — Mon, 04 Nov 2024 03:01:31 GMT

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

Re: Assign a maximum value per index

shafiz_p — Mon, 04 Nov 2024 05:03:05 GMT

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

Re: Assign a maximum value per index

Kris48 — Mon, 04 Nov 2024 06:57:19 GMT

Re: Assign a maximum value per index

shafiz_p — Mon, 04 Nov 2024 07:37:49 GMT

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.

Re: Assign a maximum value per index

Kris48 — Mon, 04 Nov 2024 07:40:55 GMT

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

Re: Assign a maximum value per index

Kris48 — Tue, 05 Nov 2024 11:21:46 GMT

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

Re: Assign a maximum value per index

shafiz_p — Tue, 05 Nov 2024 12:58:07 GMT

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?

Re: Assign a maximum value per index

Kris48 — Tue, 05 Nov 2024 17:01:32 GMT

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