Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
imranamikhan
Helper V
Helper V

Return value from column based on today's date

Hi everyone,

 

Background/Context:

 

I have the following sample table:

 

table.PNG

 

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

1 ACCEPTED SOLUTION
Mohammad_Refaei
Solution Specialist
Solution Specialist

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 )
    )
)

 

View solution in original post

3 REPLIES 3
imranamikhan
Helper V
Helper V

Thank you!

My pleasure

Mohammad_Refaei
Solution Specialist
Solution Specialist

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 )
    )
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.