Return date based on a condition in Power BI using DAX

Question

hope you can help me. I need to calculate in Power BI a date difference between today() and a certain date based on a condition.

I have a calendar table with the date (calendario[fecha]) related to a fact table ASID to predict column ASID[amount] and a measeure [Estimado] that gives me the linear regression

    Estimado = 
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'calendario'[fecha] ),
            "Known[X]", calendario[fecha],
            "Known[Y]", [ASID]
        ),
        AND (
            NOT ( ISBLANK ( Known[X] ) ),
            NOT ( ISBLANK ( Known[Y] ) )
        )
    )
VAR Count_Items =
    COUNTROWS ( Known )
VAR Sum_X =
    SUMX ( Known, Known[X] )
VAR Sum_X2 =
    SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y =
    SUMX ( Known, Known[Y] )
VAR Sum_XY =
    SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
    AVERAGEX ( Known, Known[X] )
VAR Average_Y =
    AVERAGEX ( Known, Known[Y] )
VAR Slope =
    DIVIDE (
        Count_Items * Sum_XY - Sum_X * Sum_Y,
        Count_Items * Sum_X2 - Sum_X ^ 2
    )
VAR Intercept =
    Average_Y - Slope * Average_X
RETURN
    ROUND(
        SUMX (
            DISTINCT ( calendario[fecha] ),
            Intercept + Slope * calendario[fecha]
    ),0)

My visualization matrix has 3 columns: calendario[fecha], it's real value [ASID] and the estimated measure [Estimado]. I have a limit of 1105 for that ASID. I can see that at a future day, let's say a month from now 03/12/2020, the estimated reaches a value of 1105 (after scrolling all the matrix), so I need a way to capture that day and be able to calculate 03/12/2020 - today() and display somewhere: "30 days left"

Raihan: I could use the datediff as you suggested matrix Is there a way to capture just the 231 value?

DAX is now: if([Estimado]>1105, DATEDIFF(TODAY(),LASTDATE(calendario[fecha]),DAY),0)

to answer your updated question to capture the single value of the date differences please refer to my edited answer. — Raihan

Raihan Raihan · Accepted Answer · 2020-11-03T11:46:11

As you didn't provide the sample dataset and you didn't tell about your measure formula I just consider a sample dataset on my own to simulate your problem.

Consider following screenshot with data and calculated columns.

Here the DaysFromToday calculate the Day difference from Today to for every column if the corresponding value in 'SomeValue' field reached a certain number. SomeValue is also calculated field that you can replace with your own calculation.

To get the single value from DaysFromToday you can have a measure which will give you MAX or MIN (of course some others functions if you need) of the column values like following screenshot -

Yellow highlighted is the DAX way of mentioning a field name with the table name that you are missing in your formula.

Lastly the MAX or MIN measure can be displayed in the report like following -

Return date based on a condition in Power BI using DAX

2 Answers