bitterjam
bitterjam

Reputation: 143

Power BI Calculated Column based on max date and a filter

I have a table that looks as follows:

enter image description here

I am trying to do a calculated column, so that it is 1 when Attribute 1 is 'Actual' and is at the latest available date (eg 3 Nov in this example). The following DAX calculated column does not work, would anyone know why?

LastDateFilter = 
VAR MaxDate =
    CALCULATE (
        MAX ( 'Table'[Date]),'Table'[Attribute 1]="Actual")

RETURN
    IF ('Table'[Date] = MaxDate, 1 ,0)

Upvotes: 0

Views: 2457

Answers (1)

Ljg
Ljg

Reputation: 141

If I understood properly your question, you want a result like the image that you have provided.

I have changed some names and dates but keeping the structure.

This is my approach:

LastDateFilter = 
VAR MaxDate =
   CALCULATE (
     MAX ( 'Tabla'[Date]),FILTER('Tabla','Tabla'[At1]="Actual" && NOT 
     ISBLANK('Tabla'[At2])))

RETURN
    IF (('Tabla'[Date] == MaxDate &&'Tabla'[At1]=="Actual"), 1 ,0)

enter image description here

I put filter for clarify but is not necessary... If you add data it works:

enter image description here

Upvotes: 3

Related Questions