Calculated column on date range

[MaxSchrijen23]

Hi Guys,

At the moment I am really struggling with the following, any help would be appreciated.

As input data I have a table that looks like the following, it stores for each project a value for a category and when this value has been changed:

Then in a Power BI desktop file I want to create a table storing one line for each project that calculates how the categories have changed for a given daterange. So, based on the daterange, that can be adjusted via a slicer in power bi, it should calculate the min and max date and select the value for these dates. To eventually calculate if it goes up, down or stayed the same. Up if MinDate is Red, MaxDate is Green, Down if MinDate is Red and MaxDate is Green, Same if MinDate == MaxDate. Below is a screenshot of how I want it to look based on the testinput I gave. 

So, for project 1 the mindate in the given date range for category A is 03/01/2022 which has value GREEN (see input table), the maxdate in the range for A is 14/01/2022 which has value RED (see input table), so it went Down in the given daterange.  

Many thanks in advance!!

[v-yiruan-msft · Accepted solution]

Hi  @MaxSchrijen23 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. Please create a measure as below, since you want the values change base on the date slicer selections....

Measure = 
VAR _selproject =
    SELECTEDVALUE ( 'Table'[ProjectName] )
VAR _selcat =
    SELECTEDVALUE ( 'Table'[Category] )
VAR _mindate =
    CALCULATE (
        MIN ( 'Table'[ChangeDate] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ProjectName] = _selproject
                && 'Table'[Category] = _selcat
        )
    )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[ChangeDate] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ProjectName] = _selproject
                && 'Table'[Category] = _selcat
        )
    )
VAR _mindvalue =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[ProjectName] = _selproject
                && 'Table'[Category] = _selcat
                && 'Table'[ChangeDate] = _mindate
        )
    )
VAR _maxdvalue =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[ProjectName] = _selproject
                && 'Table'[Category] = _selcat
                && 'Table'[ChangeDate] = _maxdate
        )
    )
RETURN
    SWITCH (
        TRUE (),
        _mindvalue = "Red"
            && _maxdvalue = "Green", "Up",
        _mindvalue = "Green"
            && _maxdvalue = "Red", "Down",
        _mindvalue = _maxdvalue, "Same"
    )

And you can review the following links to get more details on the difference between calculated column and measure.

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[MaxSchrijen23]

Thanks, your solution works perfect! 

There is however one small thing, when I apply this to an XLS based input it works perfect but when I try to apply the exact same logic to on input coming from a live connection it does only work for some items. Could you explain this?

[amitchandak]

@MaxSchrijen23 , Seem like you need to refer to two date range slicers. Refer if this can help

How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg

if one table is the independent date table then for second range

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = _max -5
return
calculate( sum(Table[Value]), filter(all('Date'), 'Date'[Date] >=_min && 'Date'[Date] <=_max))

the other range should work as it coming from connected date table

[MaxSchrijen23]

Thanks for your reply!

It is only net yet clear to me what you mean exactly, could you maybe attach a PBI file with an example of how it would look? And why do I need two slicers, cant I have one slicer showing a date range and then select the maximum and minimum date in between this range?

Next to this, I am really struggling with then returning the value that belong to this min and max date, could you also explain that?

Kind regards