Power Bi DAX: Relative Date Filtering

0
votes

I have a quick question, is there a way to filter data from set days between 2 months?

E.g filter from 16-15 of Jan-Feb.

Is this possible?

For example i have used a measure to calculate days between dates

Last Assessment 16-15 
    = CALCULATE(SUM('Table1'[Duration1]),'Table1'[Start], 
             DATESBETWEEN('Calendar'[Date], [Assessment Date], [Assessment one month]))

Assessment Date = if(DAY(TODAY())<16,DATE(YEAR(TODAY()),MONTH(TODAY())-1,15),
                      DATE(YEAR(TODAY()),MONTH(TODAY()),15))

Assessment one month = EDATE([Assessment Date],-1)+1
Assessment 6 = EDATE([Assessment Date],-6)+1
Assessment 12 = EDATE([Assessment Date],-12)+1

The last assessment does show from the 16th of 2 months ago to last months 15th e.g Dec 16th - Jan 15th. But i need to show from last 6 months and the last 1 year.

How can i work this out so i can show the Last 6 months and 1 year.

So far i have had to use a date filter to manually select the dates which i want to stop and have it be automatic.

2
powerbidaxpowerbi-desktop
how do you change the selection? Slicer saying 1/2/6/12 months? Current month is based on TODAY(), correct? – Stachu
What do you mean by how do i change the selection? Selection of what? and no i am not using a slicer, i am trying to make the report fully automated so i do not have to change any slicers or filters each time i use it. Current month is based on today () but the general gist of it is that, if we are before the 15th of the current month, then it will show for the last month, if we are past the 15th then it will show the current month. That is our assessment date. Right now it will be Dec 16th - Jan 15th as we have not passed the 15th of Feb. – user12504122
Sorry, I thought you wanted to have a single measure [Assessment] that would calculate for either 1/6/12 months range based on a slicer selection. But is seems you need measure for each specific period, correct? – Stachu
That is correct. If you read my post then you would see that there are different Assessment measures – user12504122
check out my answer, I think it covers your requirement as I understood it. It can also be easily converted to one measure working for multiple periods at the same time, but then you need a flag that would specify number of months – Stachu

2 Answers

1
votes

If it is just the last 6 months or the last year you could make a custom column in the query editor (this would be the easiest way then). Like a filter flag:

'Includes the current month
Last 6 Months Flag = Date.IsInPreviousNMonths([YourDate], 6) or Date.IsInCurrentMonth([YourDate])

'Without the current month
Last 6 Months Flag = Date.IsInPreviousNMonths([YourDate], 6)

The same for last year:

Last Year Flag = Date.IsInPreviousYear([YourDate])

Drag and drop these custom columns as filter on your report and you are done.

1
votes

consider following measure for Latest assessment (basically what you had before with EDATE for safe Dec/Jan handling and with variables for better performance)

Latest Assessment Date = 
VAR __today = TODAY()
RETURN
IF (
    DAY ( __today ) < 16,
    DATE ( YEAR ( __today ), MONTH(EDATE(__today, - 1)), 15 ),
    DATE ( YEAR ( __today ), MONTH ( __today ), 15 )
)

then you have this measure for handling 1/6/12 scenarios, you just replace 12 with whatever number of months you need

Measure 12 =
VAR __nrOfMonthsBack = 12
VAR __lastAssesment = [Latest Assessment Date]
RETURN
    CALCULATE (
        SUM ( Table1[Duration] ),
        ALL('Calendar'),  --this will reset any date filters coming from slicers
        DATESBETWEEN (
            'Calendar'[Date],
            EDATE ( __lastAssesment, - __nrOfMonthsBack ) + 1,
            __lastAssesment
        )
    )

EDIT: added ALL('Calendar') to disable filters coming from slicers