Return value from column based on today's date

[imranamikhan]

Hi everyone,

Background/Context:

I have the following sample table:

Date Series: a column which contains an iterative list of week ending dates.

Baseline: for each row, count the number of items in a separate table where the Date column in that table is greater than or equal to the Date Series column in this table (I can build this column using a measure or a calculated column)

Forecast: for each row, count the number of items in a separate table where the Date column in that table is greater than or equal to the Date Series column in this table + an additional parameter (I can build this column using a measure or a calculated column)

Problem:

From the table above, I want to return the Baseline value and Forecast value associated to today’s week ending date because I want use them for a Card visual and Gauge visual.

If I was using Excel, I would use the formula like below:

=IF(MAX([Date Series Column])<=[W/E Today],INDEX([Baseline Column],MATCH(MAX([Date Series Column]),[Date Series Column],0)),INDEX([Baseline Column],MATCH[W/E Today], [Date Series Column],0)))

This formula reads: If the today’s week ending date is less than or equal to the maximum date in the Date Series column, then return the Baseline value from the Max Date, otherwise return the Baseline value based from the row which is equal to today’s week ending date.

Could anyone assist in recreating this in DAX?

best regards,

AmiK

[imranamikhan]

Thank you!

[Mohammad_Refaei]

My pleasure

[Mohammad_Refaei · Accepted solution]

Try this if you want to filter Sunday:

CalculatedBaseline =
CALCULATE (
    SUM ( Table[Baseline] ),
    FILTER (
        ALL ( Table ),
        Table[Date Series]
            = TODAY () + 7
                - WEEKDAY ( TODAY (), 2 )
    )
)

For Saturday:

CalculatedBaseline =
CALCULATE (
    SUM ( Table[Baseline] ),
    FILTER (
        ALL ( Table ),
        Table[Date Series]
            = TODAY () + 7
                - WEEKDAY ( TODAY (), 1 )
    )
)