Same period or days range for last six months

[tskumar]

Request  for guidance on how to apply filter on dates from 1st to CurrentDate/Max Date in current month for last six months and sum the values
For example:
Current Date is : 21 July 2022
so the filter should be : 1 Jun 2022 to 21 Jun 2022, 1 May 2022 to 21 May 2022, 1 Apr 2022 to 21 Apr 2022 so on...

Thank you

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message.

I think there are many ways to restrict to show until the data is available.

Please check the below measure and the attached file. I amended a little to show up to available date.

Same days range last six months: =
VAR _availablelastdate =
    CALCULATE (
        MAXX (
            FILTER ( VALUES ( 'Calendar'[Date] ), [Quantity measure:] <> BLANK () ),
            'Calendar'[Date]
        ),
        ALL ( 'Calendar' )
    )
VAR _currentdaynumber =
    DAY ( MAX ( 'Calendar'[Date] ) )
VAR _endofdatepreviousmonth =
    CALCULATE (
        MAX ( 'Calendar'[End of Month] ),
        DATEADD ( 'Calendar'[Date], -1, MONTH )
    )
VAR _endofdatesixmonthsbefore =
    CALCULATE (
        MAX ( 'Calendar'[End of Month] ),
        DATEADD ( 'Calendar'[Date], -6, MONTH )
    )
VAR _filtercalendar =
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[End of Month] >= _endofdatesixmonthsbefore
            && 'Calendar'[End of Month] <= _endofdatepreviousmonth
            && DAY ( 'Calendar'[Date] ) <= _currentdaynumber
    )
RETURN
    IF (
        MAX ( 'Calendar'[Date] ) <= _availablelastdate,
        IF (
            _endofdatepreviousmonth <> BLANK ()
                && _endofdatesixmonthsbefore <> BLANK (),
            CALCULATE ( [Quantity measure:], _filtercalendar )
        )
    )

[tamerj1 · Accepted solution]

Hi @tskumar 

Here is a sample file with the solution https://we.tl/t-KPniHplng5

You need to have a previous date table which is just a shadow copy of the date table. The relationships are shown below. 

This is the code of the measure. The sample file contains old data so I used fixed date in the code but in your case you need to replace this date with TODAY ( )

Sales Previous 6 Months Same Period = 
VAR RefDate = DATE (2009, 7, 15 ) -- Use 'TODAY ( )'
VAR PreviousDates =
    DATESINPERIOD ('Previous Dates'[Date], RefDate, - 6, MONTH )
VAR FilteredPerviousDates =
    FILTER ( PreviousDates, DAY ( 'Previous Dates'[Date] ) <= DAY ( RefDate ) )
VAR Result =
    CALCULATE ( 
        [Sales Amount],
        REMOVEFILTERS ( 'Date' ),
        KEEPFILTERS ( FilteredPerviousDates ),
        USERELATIONSHIP ( 'Date'[Date], 'Previous Dates'[Date] )
    )
RETURN
    Result

[tskumar]

Thank you Jihwan_Kim for the quick response and solution. It will definitely help me,  one small concern is I have to select the date to show the MTD value, it would be great if it could detect the last available date. 
For example if the data is available till December 15th only, then it should show till that date instead of rest of the dates as well  as shown in the image below   

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message.

I think there are many ways to restrict to show until the data is available.

Please check the below measure and the attached file. I amended a little to show up to available date.

Same days range last six months: =
VAR _availablelastdate =
    CALCULATE (
        MAXX (
            FILTER ( VALUES ( 'Calendar'[Date] ), [Quantity measure:] <> BLANK () ),
            'Calendar'[Date]
        ),
        ALL ( 'Calendar' )
    )
VAR _currentdaynumber =
    DAY ( MAX ( 'Calendar'[Date] ) )
VAR _endofdatepreviousmonth =
    CALCULATE (
        MAX ( 'Calendar'[End of Month] ),
        DATEADD ( 'Calendar'[Date], -1, MONTH )
    )
VAR _endofdatesixmonthsbefore =
    CALCULATE (
        MAX ( 'Calendar'[End of Month] ),
        DATEADD ( 'Calendar'[Date], -6, MONTH )
    )
VAR _filtercalendar =
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[End of Month] >= _endofdatesixmonthsbefore
            && 'Calendar'[End of Month] <= _endofdatepreviousmonth
            && DAY ( 'Calendar'[Date] ) <= _currentdaynumber
    )
RETURN
    IF (
        MAX ( 'Calendar'[Date] ) <= _availablelastdate,
        IF (
            _endofdatepreviousmonth <> BLANK ()
                && _endofdatesixmonthsbefore <> BLANK (),
            CALCULATE ( [Quantity measure:], _filtercalendar )
        )
    )

[Jihwan_Kim]

Hi,

I tried to create a sample pbix file like below.

I suggest having a calednar dimension table, and then writing a measure something like below.

I hope the below can provide some ideas on how to create a solution for your dataset.

Same days range last six months: =
VAR _currentdaynumber =
    DAY ( MAX ( 'Calendar'[Date] ) )
VAR _endofdatepreviousmonth =
    CALCULATE (
        MAX ( 'Calendar'[End of Month] ),
        DATEADD ( 'Calendar'[Date], -1, MONTH )
    )
VAR _endofdatesixmonthsbefore =
    CALCULATE (
        MAX ( 'Calendar'[End of Month] ),
        DATEADD ( 'Calendar'[Date], -6, MONTH )
    )
VAR _filtercalendar =
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[End of Month] >= _endofdatesixmonthsbefore
            && 'Calendar'[End of Month] <= _endofdatepreviousmonth
            && DAY ( 'Calendar'[Date] ) <= _currentdaynumber
    )
RETURN
    IF (
        _endofdatepreviousmonth <> BLANK ()
            && _endofdatesixmonthsbefore <> BLANK (),
        CALCULATE ( [Quantity measure:], _filtercalendar )
    )