Date Measure across end of year

[aflintdepm]

I keep running into this issue and I'm hoping that there is a standardized way to do this.

My company measures a lot of data by "date of event".  That data is then tabulated by current period and prior period (e.g. current week/prior week, current month/prior month).  Every year, in January, we run into an issue because the prior week/month is also in a different year.

So, a formula like

=IF(MONTH([Event Date])=MONTH(TODAY()),"CURRENT",IF(MONTH([Event Date])=MONTH(TODAY())-1,"LAST","OLDER") 

breaks when Dec (12) and Jan (1) are the elements because 12 <> 1-1

Is there a standard way of handling this?

[Anonymous]

Hi @aflintdepm

For your question about the previous week/month, not being in the same year as the current date, here is a workaround I offer:

Here's some dummy data

"Table"

For testing purposes I selected the dates in January via the slicer.

_Date = 
    var _date = 
        IF(
            ISFILTERED('Table'[Date]), 
            MIN('Table'[Date]), 
            BLANK()
        )
    RETURN _date

You can try using the EOMONTH function. It returns the date of the last day of the month before or after the specified number of months. For example, the month before 1/3/2024, the query result is 12/31/2023:

LAST_DATE = EOMONTH([_Date], -1)

You can create a measure. Calculate the sum of the previous month.

Last_Month_Value_Total = 
    var LAST_DATE = EOMONTH([_Date], -1)
RETURN 
    CALCULATE(
        SUM('Table'[values]), 
        FILTER(
            ALL('Table'), 
            MONTH('Table'[Date]) = MONTH(LAST_DATE) 
            && 
            YEAR('Table'[Date]) = YEAR(LAST_DATE)
        )
    )

Here is the result.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aflintdepm]

Thank you for providing so many examples. I will need to work thru these and see which one works.  I also need a way to do this for weeks, such as Week 1 in January vs. Week 52 in December. 

I apologize for not being more clear- I hoped there was some standard formula or model that addressed this situation.

I'll try to come up with a way to clarify my question and add to this thread when I do.  Thank you again for your help.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...