Forum Discussion

Kris48's avatar
Kris48
Frequent Visitor
1 year ago
Solved

Assign a maximum value per index

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 

 

DAX
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    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

8 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    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

      • shafiz_p's avatar
        shafiz_p
        Super User
        1 year ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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