DAX Formula to filter the last 28 days in the table data

Goutham Anil 0
Viewed 65

I have a measure that totals the values for each date in the table. I want to filter this measure so that I can display only the last 28 days present in the table instead of displaying values for all the dates. Following is the code that works for getting totals for full table:

CALCULATE( SUM(Daily_Reports[Confirmed]), 
FILTER( ALL(Daily_Reports), 
    Daily_Reports[Case_Date] = SELECTEDVALUE(Daily_Reports[Case_Date]) ) )

The 'relative date' filter in the Filters pane does not work because it only accepts the last 28 days based on today's date and not the dates in the table. Please suggest a DAX formula that can filter for the last 28 days present in the table.

powerbi-desktop powerbi
3 Answers

Try this code

VAR endDay = LastDate(Daily_Reports[Case_Date])
VAR startDay= DATEADD(endDay,-28,DAY)
VAR setOfDates = DATESBETWEEN(Daily_Reports[Case_Date], StartDate, EndDate ) 

RETURN
    CALCULATE(
        SUM(Daily_Reports[Confirmed])
        ,setOfDates 
    )
userLOTh10

You can try this one:

MS =
CALCULATE (
    SUM ( Daily_Reports[Confirmed] ),
    FILTER (
        ALL ( Daily_Reports[Case_Date] ),
        Daily_Reports[Case_Date]
            >= SELECTEDVALUE ( Daily_Reports[Case_Date] ) - 28
    )
)
Ozan Sen0

This is what finally worked for me. I created a measure (not a column), that returns 1 for the last 28 days with an IF clause, leaving it blank if the date is not in the last 28 days as follows:

Last28 = 
VAR MaxDate = LASTDATE( ALL(Daily_Reports[Case_Date]) ) 
VAR MinDate = DATEADD( MaxDate, -28, DAY )

RETURN
IF( SELECTEDVALUE(Daily_Reports[Case_Date]) >= MinDate && SELECTEDVALUE(Daily_Reports[Case_Date]) <= MaxDate, 1 )

Then I incorporated this measure into the Calculate function as follows:

Daily Cases = 
CALCULATE( SUM(Daily_Reports[Confirmed]), 
    FILTER( ALL(Daily_Reports), 
        Daily_Reports[Case_Date] = SELECTEDVALUE(Daily_Reports[Case_Date]) && NOT(ISBLANK(Daily_Reports[Last28])) 
    ) 
)
Goutham Anil0
Related